To help you understand SQL I should point out that your version would
work (assuming only 1 school per person) if you just left the friends
out of the FROMs for the sub-selects:-
select frienda, friendb from friends where
(select schools.school from schools as schoolsa where friends.frienda =
schools.person) =
(select schools.school from schools as schoolsb where friends.friendb =
schools.person);
This is because the only thing that the sub-selects need to know from
friends is the person to retrieve for, and that comes from the WHERE
clauses.
Although what Josh said is correct, and his is a better solution as it
can cope with the 2+ schools per person problem.
Josh Berkus wrote:
>
snip...
> Mike,
>
> > select frienda, friendb from friends where (select
> > schools.school from friends,schools where friends.frienda =
> > schools.person) = (select schools.school from friends,schools where
> > friends.friendb = schools.person);
>
> Too complicated. You need to learn how to use JOINS and table aliases
> (or find yourself some friends who know SQL!):
>
> SELECT friends.frienda, friends.friendb
> FROM friends JOIN schools schoola ON friends.frienda = schoola.person
> JOIN schools schoolb ON friends.friendb = schoolb.person
> WHERE schoola.school = schoolb.school
>
> and, if it's possible that any particular person went to more than one
> school, add:
>
> GROUP BY frienda, friendb
>
> Simple, neh?
>
> -Josh
snip..
--
This is the identity that I use for NewsGroups. Email to
this will just sit there. If you wish to email me replace
the domain with knightpiesold . co . uk (no spaces).