Date: Mon, 25 Aug 2003 20:12:13 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Joseph Shraibman <jks@selectacast.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: How to join from two tables at once?
In-Reply-To: <biebbh$2krq$1@news.hub.org>
Message-ID: <20030825201102.T11277-100000@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Mon, 25 Aug 2003, Joseph Shraibman wrote:

> 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;

Probably you want something like:
SELECT u.uid, u.txt, p.val FROM
 u INNER JOIN a ON (a.id=u.aid)
 LEFT JOIN p ON (p.uid=u.uid AND p.pkey=a.pkey);