Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f98D4nn53245 for ; Mon, 8 Oct 2001 09:04:49 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f98Cb1t28634 for pgsql-sql@postgresql.org; Mon, 8 Oct 2001 08:37:01 -0400 (EDT) (envelope-from news) From: "Thurstan R. McDougle" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: SQL Syntax / Logic question Date: Mon, 08 Oct 2001 13:36:29 +0100 Message-ID: <3BC19DCD.1249351E@my-deja.com> References: X-NNTP-Posting-Host: mail.groundsolutions.co.uk:193.195.66.163 X-Complaints-To: abuse@demon.net X-Mailer: Mozilla 4.77 [en] (Win98; U) X-Accept-Language: en,pdf MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 50 To: pgsql-sql@postgresql.org X-Archive-Number: 200110/116 X-Sequence-Number: 5071 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).