Re: How to find Brothers and Sisters?

From: Cimode <cimode_at_hotmail.com>
Date: 3 Dec 2006 03:40:54 -0800
Message-ID: <1165146054.307356.145510_at_j44g2000cwa.googlegroups.com>


Neo a écrit :

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

I realize I made a mistake...

Please repackage
<<
create view as Q_B
SELECT DISTINCT person.id
 FROM link INNER JOIN person ON link.child = person.id  WHERE (((person.name)='John'))>>

then

select P2.name from person P2
inner join link on Q_B B on P2.parent = B.id where P2.name <> 'John'

Should be sufficient to get all childs of John's parent... (If I am not mistaken..) Received on Sun Dec 03 2006 - 12:40:54 CET

Original text of this message