From: Joseph Shraibman <jks@selectacast.net>
To: pgsql-sql@postgresql.org
Subject: How to join from two tables at once?
Date: Mon, 25 Aug 2003 20:58:29 -0400
Message-ID: <biebbh$2krq$1@news.hub.org> (raw)
How can I join on one table with join conditions refering to two tables? In this example
p is missing an entry that corresponds to u. I want to select from u and p, but have
entries in u that don't have an entry in p. The problem is I need to go through table a
to get the corresponding value in table p, and a LEFT JOIN only operates on two tables.
The subselect works, but in real life turns out to be a big performance drain.
---------------------------------
example:
begin;
create table u (uid int, aid int, txt text);
create table a (id int, pkey int);
create table p (uid int, pkey int, val text);
insert into u VALUES(1,1,'one');
insert into u VALUES(2,1,'two');
insert into u VALUES(3,1,'three');
insert into a VALUES(1, 9);
insert into p VALUES(1,9,'ONE');
insert into p VALUES(3,9,'THREE');
-- doesn't get 2, because there is no entry in p for it
SELECT u.uid, u.txt, p.val FROM u,a,p WHERE u.uid = p.uid and a.id = u.aid and p.pkey =
a.pkey;
-- works, but uses a subselect
SELECT u.uid, u.txt, (SELECT p.val FROM p WHERE p.uid = u.uid AND p.pkey = a.pkey) FROM
u,a WHERE a.id = u.aid;
--doesn't work: ERROR: JOIN/ON clause refers to "u", which is not part of JOIN
SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND p.pkey = a.pkey
WHERE a.id = u.aid;
abort;
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: jks@selectacast.net
Subject: Re: How to join from two tables at once?
In-Reply-To: <biebbh$2krq$1@news.hub.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