pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL Syntax / Logic question
3+ messages / 3 participants
[nested] [flat]

* SQL Syntax / Logic question
@ 2001-10-04 14:27 Michael D. Harlan <r3mdh@beechwoodplace.org>
  2001-10-04 15:02 ` Re: SQL Syntax / Logic question Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael D. Harlan @ 2001-10-04 14:27 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL Syntax / Logic question
  2001-10-04 14:27 SQL Syntax / Logic question Michael D. Harlan <r3mdh@beechwoodplace.org>
@ 2001-10-04 15:02 ` Josh Berkus <josh@agliodbs.com>
  2001-10-08 12:36   ` Re: SQL Syntax / Logic question Thurstan R. McDougle <trmcdougle@my-deja.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2001-10-04 15:02 UTC (permalink / raw)
  To: Michael D. Harlan <r3mdh@beechwoodplace.org>; pgsql-sql

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

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL Syntax / Logic question
  2001-10-04 14:27 SQL Syntax / Logic question Michael D. Harlan <r3mdh@beechwoodplace.org>
  2001-10-04 15:02 ` Re: SQL Syntax / Logic question Josh Berkus <josh@agliodbs.com>
@ 2001-10-08 12:36   ` Thurstan R. McDougle <trmcdougle@my-deja.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Thurstan R. McDougle @ 2001-10-08 12:36 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-10-08 12:36 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-04 14:27 SQL Syntax / Logic question Michael D. Harlan <r3mdh@beechwoodplace.org>
2001-10-04 15:02 ` Josh Berkus <josh@agliodbs.com>
2001-10-08 12:36   ` Thurstan R. McDougle <trmcdougle@my-deja.com>

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