Apologies for the lengthy message. I'm a SQL newbie trying to model family
relationships in SQLite (and no, this isn't homework; it's part of an effort
to create a computer-based language lesson on kinship terminology). I've
created the following tables:
people (a table of individuals who are somehow interrelated)
fields: uid (among others)
partners (links individuals [i.e., individual spouses] to couple IDs)
fields: coupleid, personid
children (links children to their parents' couple ID)
fields: coupleid, personid
In theory, these tables should contain enough data to identify a number of
relationships between the different individuals in the database. For
example, I can link parents and children using the following query:
SELECT
p.uid AS personid,
c.coupleid,
t.personid AS parentid,
FROM
people AS p,
children AS c,
partners AS t,
people AS p2
WHERE
p.uid = c.personid AND
c.coupleid = t.coupleid AND
p2.uid = t.personid
Defining the above query as a view named "parentage", I can link
grandparents and grandchildren as follows:
SELECT
gc.personid,
gc.coupleid AS parentcoupleid,
gp.parentid AS grandparentid,
gp.coupleid AS grandparentcoupleid
FROM
parentage AS gc,
parentage AS gp
WHERE
gc.parentid = gp.personid
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. I know SQL provides a COUNT function and can
compute the intersection of two queries, and I assume I can use those
together (i.e., if the intersection of the parents of two individuals is an
empty set, they can't be siblings). But I can't figure out how to
incorporate COUNT and INTERSECT into the above query to filter out siblings
and identical individuals. Is that possible? If so, how would I go about
doing that?
I'd also be grateful for any criticism of my queries, if anything strikes
you as bad or ugly.
Thanks,
Aric
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
