pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thurstan R. McDougle <trmcdougle@my-deja.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL Syntax / Logic question
Date: Mon, 08 Oct 2001 13:36:29 +0100
Message-ID: <3BC19DCD.1249351E@my-deja.com> (raw)
References: <web-141646@davinci.ethosmedia.com>

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



view thread (3+ messages)

Message-ID: <3BC19DCD.1249351E@my-deja.com>
Permalink:  ../3BC19DCD.1249351E@my-deja.com/
Also on:    postgresql.org/message-id/3BC19DCD.1249351E@my-deja.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: trmcdougle@my-deja.com
  Subject: Re: SQL Syntax / Logic question
  In-Reply-To: <3BC19DCD.1249351E@my-deja.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox