How to join from two tables at once?
От | Joseph Shraibman |
---|---|
Тема | How to join from two tables at once? |
Дата | |
Msg-id | biebbh$2krq$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: How to join from two tables at once?
Re: How to join from two tables at once? |
Список | pgsql-sql |
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;
В списке pgsql-sql по дате отправления: