Thread: SQL Syntax / Logic question

I've been working on this SQL problem for about 12 days now and have asked
for help from friends/colleagues, but haven't found a solution.  I send it
to this list as a last resort.

Let's say I have a table called "friends" and in this table, I have the
following data:

FriendA  FriendB
-------  -------
Mike     Christopher
Jim      Mike
Joe      Sara
Jim      Sara

Let's also say I have another table called "schools" and in this table, I
have the following data:

Person        School
------        ------
Christopher    Akron
Mike        Akron
Jim        OSU
Joe        Kent
Sara        OSU


I want to be able to return all (FriendA, FriendB) pairs in which both
friends went to the same school.  The above example would return only 
these pairs:

Mike, Christopher
Jim, Sara


My initial thinking was that I need a query like this:

select frienda,friendb from friends where "frienda's school" = "friendb's
school";


Translating the pseudo-code into a real query, we have:

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


Of course, this doesn't work in real life.  I get the usual error:

ERROR:  More than one tuple returned by a subselect used as an expression.


Is there a way to do this or am I asking for the impossible?


Many thanks for any help you can provide.


Mike Harlan
r3mdh@beechwoodplace.org

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

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

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