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); ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
