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

Reply via email to