Re: How to find Brothers and Sisters?
Date: 2 Dec 2006 13:45:15 -0800
> > Just for sisters...
> > 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 = 'John') B
> > on P2.id = B.id
> > where P2.name <> 'John'
> > and P2.sex='Female'
> Forgot to mention that the query I provided is SQL Server compliant .I do not think Access does JOIN on selects. You may need to package the inner select in a view then do a JOIN on that view...
Updated original query as follows:
from person P2 inner join link on P2.id = link.parent inner join Q_B B on P2.id = B.id
where P2.name <> 'John'
However I still get the previous missing operator error. The SQL Server query may not be appropriate. At the very least, on the second line of query, "P2.id = link.parent" should be "P2.id = link.child" because mary joins to link table via link.child not link.parent. In addition, the inner select should probably get parent's id from link table and not john's id from person table. How does this query ensure that siblings share a common parent? Could someone explain. Received on Sat Dec 02 2006 - 22:45:15 CET