pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael D. Harlan <r3mdh@beechwoodplace.org>
To: pgsql-sql@postgresql.org
Subject: SQL Syntax / Logic question
Date: Thu, 4 Oct 2001 10:27:12 -0400
Message-ID: <20011004102712.A29819@beechwoodplace.org> (raw)

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



view thread (3+ messages)  latest in thread

Message-ID: <20011004102712.A29819@beechwoodplace.org>
Permalink:  ../20011004102712.A29819@beechwoodplace.org/
Also on:    postgresql.org/message-id/20011004102712.A29819@beechwoodplace.org

 · 

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: r3mdh@beechwoodplace.org
  Subject: Re: SQL Syntax / Logic question
  In-Reply-To: <20011004102712.A29819@beechwoodplace.org>

* 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