Thread: How to join from two tables at once?

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;

On Tuesday 26 August 2003 02:58, 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.
(...)

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

Try:

SELECT u.uid, u.txt, p.val FROM uINNER JOIN a ON (a.id=u.aid) LEFT JOIN p ON (p.pkey=a.pkey AND p.uid=u.uid)


Ian Barwick
barwick@gmx.net

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 FROMu INNER JOIN a ON (a.id=u.aid)LEFT JOIN p ON (p.uid=u.uid AND p.pkey=a.pkey);

Stephan Szabo wrote:

> 
> 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);
From the docs:
 A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from 
listing the two items at the top level of FROM. CROSS JOIN is equivalent to INNER JOIN ON 
(TRUE), that is, no rows are removed by qualification. These join types are just a 
notational convenience, since they do nothing you couldn't do with plain FROM and WHERE.

... so obviously there *is* something that INNER JOIN can do that regular ANDs can't.  But 
I'm still not clear why one works and the other doesn't.