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
