Received: from challenger.beechwoodplace.org (c11b012.neo.rr.com [204.210.203.12]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f94ERMn00238 for ; Thu, 4 Oct 2001 10:27:26 -0400 (EDT) (envelope-from r3mdh@challenger.beechwoodplace.org) Received: (from r3mdh@localhost) by challenger.beechwoodplace.org (8.11.6/8.9.3) id f94ERDL30091 for pgsql-sql@postgresql.org; Thu, 4 Oct 2001 10:27:13 -0400 (EDT) (envelope-from r3mdh) Date: Thu, 4 Oct 2001 10:27:12 -0400 From: "Michael D. Harlan" To: pgsql-sql@postgresql.org Subject: SQL Syntax / Logic question Message-ID: <20011004102712.A29819@beechwoodplace.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i X-Archive-Number: 200110/48 X-Sequence-Number: 5003 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