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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users