On 24 Feb 2011, at 07:49, Aric Bills wrote:
> 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
Cousin's IDs is tricky, if you want to do it absolutely correctly; easier if
you can assume that there have been no incestuous relationships. The key is
that you need all the relevant relatives in a single row. I'll give it a go...
SELECT PersonA.personID, PersonB.personID
FROM Children AS PersonA,
INNER JOIN Partners AS PartnersA ON (PersonA.CoupleID =
PartnersA.CoupleID)
INNER JOIN Children AS Parent1A ON (ParentsA.personID =
PartnersA.personID)
INNER JOIN Children AS Parent2A ON (Parent1A.personID >
Parent2A.personID AND ParentsA.personID = PartnersA.personID)
INNER JOIN Partners AS GPartners1A ON (GPartners1A.coupleID =
Parent1A.coupleID)
INNER JOIN Partners AS GPartners2A ON (GPartners2A.coupleID =
Parent2A.coupleID)
Children AS PersonB,
INNER JOIN Partners AS PartnersB ON (PersonB.CoupleID =
PartnersB.CoupleID)
INNER JOIN Children AS Parent1B ON (ParentsB.personID =
PartnersB.personID)
INNER JOIN Children AS Parent2B ON (Parent1B.personID >
Parent2B.personID AND ParentsB.personID = PartnersB.personID)
INNER JOIN Partners AS GPartners1B ON (GPartners1B.coupleID =
Parent1B.coupleID)
INNER JOIN Partners AS GPartners2B ON (GPartners2B.coupleID =
Parent2B.coupleID)
WHERE PersonA.personID > PersonB.personID
AND (GPartners1A.personID = GPartners1B.personID
OR GPartners2A.personID = GPartners1B.personID
OR GPartners1A.personID = GPartners2B.personID
OR GPartners2A.personID = GPartners2B.personID
)
AND NOT (Parent1A.personID = Parent1B.personID
OR Parent2A.personID = Parent1B.personID
OR Parent1A.personID = Parent2B.personID
OR Parent2A.personID = Parent2B.personID
)
AND NOT (GPartners1A.personID = Parent1B.personID
OR GPartners2A.personID = Parent1B.personID
OR GPartners1A.personID = Parent2B.personID
OR GPartners2A.personID = Parent2B.personID
)
AND NOT (Parent1A.personID = GPartners1B.personID
OR Parent2A.personID = GPartners1B.personID
OR Parent1A.personID = GPartners2B.personID
OR Parent2A.personID = GPartners2B.personID
)
;
Best Regards,
Phil Willoughby
--
Managing Director, StrawberryCat Limited
StrawberryCat Limited is registered in England and Wales with Company No.
7234809.
The registered office address of StrawberryCat Limited is:
107 Morgan Le Fay Drive
Eastleigh
SO53 4JH
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
