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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
