From: Ant9000 <ant9000@seldati.it>
To: pgsql-sql@postgreSQL.org
Subject: OUTER JOINs in PostgreSQL
Date: Wed, 2 Jun 1999 22:47:09 +0200
Message-ID: <99060222482601.00588@chomp> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: ant9000@seldati.it
Subject: Re: OUTER JOINs in PostgreSQL
In-Reply-To: <99060222482601.00588@chomp>
References: <99060222482601.00588@chomp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox