Re: How to find Brothers and Sisters?
Date: 2 Dec 2006 05:28:34 -0800
<<<<What schema/query finds John's brothers and sisters based only on direct relationships to parent and gender>>>>
Something like this should do...
SK: Suroggate key
NN: Non NULL
name+sex uniquely identifies person
nodes: id(SK), name, sex
1(SK), Adam, Male 2(SK), John, Male 3(SK), Jack, Male 4(SK), Mary, Female
1, 2 1, 3 1, 4
Getting John's brother and sisters without involving Adam in da query
select P2.name, P2.sex from person P2
inner join link
on P2.id = link.parent
inner join (select id from link inner join person P1 on child =
person.id where person.name = 'Jonh') B
on P2.id = B.id
where P2.name <> 'John'
) Received on Sat Dec 02 2006 - 14:28:34 CET