From: | ant9000(at)c0a111(dot)science(dot)unitn(dot)it |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | OUTER JOINS in PostgreSQL |
Date: | 1999-06-02 18:30:51 |
Message-ID: | Pine.LNX.4.04.9906022013370.14777-100000@c0a111.science.unitn.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | David Sauer | 1999-06-02 19:10:04 | how to delete access rights from non-existent user ? |
Previous Message | Pham, Thinh | 1999-06-02 15:38:44 | RE: [SQL] Column name's length |