Re: How to find Brothers and Sisters?

From: Cimode <cimode_at_hotmail.com>
Date: 2 Dec 2006 05:28:34 -0800
Message-ID: <1165066114.757875.241050@80g2000cwy.googlegroups.com>

<<<<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

link:parent(FK), child(FK)
parent, child

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 - 07:28:34 CST