[SQL] How to join from two tables at once?

[Joseph Shraibman]

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;

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly