OUTER JOINs in PostgreSQL
От | Ant9000 |
---|---|
Тема | OUTER JOINs in PostgreSQL |
Дата | |
Msg-id | 99060222482601.00588@chomp обсуждение исходный текст |
Ответы |
Re: [SQL] OUTER JOINs in PostgreSQL
Re: [SQL] OUTER JOINs in PostgreSQL |
Список | pgsql-sql |
Hi, I was trying to do an apparently simple task: I have two tables, say master id | name --------- 1 | Alpha 2 | Beta 3 | Gamma detail ------- id | lastvisit | info --------------------- 1 | Wed Jun 02 19:43:08 1999 CEST | blah blah 1 | Wed Jun 02 19:45:08 1999 CEST | some more blah's Now, I'd like to list all of the fields of table 'master', together with the fields lastvisit and info from 'detail' if they have a corresponding value: ie, I'd like some SQL that gives me id | name | lastvisit | info ----------------------------- 1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah 1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's 2 | Beta | | 3 | Gamma | | The best I was able to obtain is this: SELECT master.*,detail.lastvisit,detail.info FROM master,detail WHERE master.id=detail.id UNION SELECT *,NULL AS lastvisit,NULL AS info FROM master WHERE id NOT IN (SELECT id FROM detail); which is (at best) unelegant; in MS Access you could do something like SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail ON master.id=detail.id; With Oracle, there's an even shorter solution: SELECT master.*,detail.lastvisit,detail.info FROM master,detail WHERE master.id=detail.id(+); Is anything like that available with PostgreSQL? Thanks in advance, Ant9000
В списке pgsql-sql по дате отправления: