Re: How to find Brothers and Sisters?

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

I packaged the inner expression in query Q_B as follows: SELECT person.id
FROM link INNER JOIN person ON link.child = person.id WHERE (((person.name)='John'));

Updated original query as follows:
select P2.name
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'

         and P2.sex='Female'

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