pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL 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