I would say that couple should be a 3 column table, with coupleID, partner1ID, partner2ID. It looks like right now, you have just coupleID and partnerID, that doubles the number of rows you have.
On Thu, Feb 24, 2011 at 7:02 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Aric Bills <aric.bi...@gmail.com> wrote: > > Using the results of this query, I'd like to identify cousins. It should > be > > possible to define two people as cousins if they share a grandparent but > > have no parents in common. Defining the query above as the view > > "grandparentage", I can come up with a query for people who share a > common > > grandparent as follows: > > > > SELECT > > p.personid, > > c.personid AS cousinid, > > FROM > > grandparentage AS p, > > grandparentage AS c > > WHERE > > p.grandparentid = c.grandparentid > > > > What I'm not sure how to do is determine whether p.personid has any > parents > > in common with c.personid. > > and not exists ( > select 1 from parentage parent1, parentage parent2 > where parent1.parentid = parent2.parentid > and parent1.personid = p.personid > and parent2.personid = c.personid) > > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
