pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
OUTER JOINS in PostgreSQL
6+ messages / 5 participants
[nested] [flat]

* OUTER JOINS in PostgreSQL
@ 1999-06-02 18:30  ant9000@c0a111.science.unitn.it
  0 siblings, 0 replies; 6+ messages in thread

From: ant9000@c0a111.science.unitn.it @ 1999-06-02 18:30 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* OUTER JOINs in PostgreSQL
@ 1999-06-02 20:47  Ant9000 <ant9000@seldati.it>
  0 siblings, 2 replies; 6+ messages in thread

From: Ant9000 @ 1999-06-02 20:47 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] OUTER JOINs in PostgreSQL
@ 1999-06-02 21:08  Jason Earl <jearl@box100.com>
  parent: Ant9000 <ant9000@seldati.it>
  1 sibling, 1 reply; 6+ messages in thread

From: Jason Earl @ 1999-06-02 21:08 UTC (permalink / raw)
  To: ant9000@seldati.it; +Cc: pgsql-sql

I think that you are looking for something like:

SELECT m.id, m.name, d.lastvisit, d.info
       FROM master m, detail d
       WHERE m.id = d.id;

Jason


   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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] OUTER JOINs in PostgreSQL
@ 1999-06-05 13:56  Ant9000 <ant9000@seldati.it>
  parent: Jason Earl <jearl@box100.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Ant9000 @ 1999-06-05 13:56 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Jason Earl <jearl@box100.com>

On Wed, 02 Jun 1999, you wrote:
>I think that you are looking for something like:
>
>SELECT m.id, m.name, d.lastvisit, d.info
>       FROM master m, detail d
>       WHERE m.id = d.id;
>
>Jason
>

No, that way I get an inner join: in my example the result would be

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

and I'm instead looking for this output:

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 |                                                 |




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] OUTER JOINs in PostgreSQL
@ 1999-06-06 12:06  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Ant9000 <ant9000@seldati.it>
  1 sibling, 0 replies; 6+ messages in thread

From: Herouth Maoz @ 1999-06-06 12:06 UTC (permalink / raw)
  To: Ant9000 <ant9000@seldati.it>; pgsql-sql

At 23:47 +0300 on 02/06/1999, Ant9000 wrote:


> 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?

No, outer joins are not supported in PostgreSQL. Your solution is the
accepted workaround, although I tend to recommend WHERE NOT EXISTS ( select
* from detail WHERE detail.id = master.id )in the second clause - it is
more efficient.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] OUTER JOINs in PostgreSQL
@ 1999-06-14 20:46  Fomichev Michael <fomichev@null.ru>
  parent: Ant9000 <ant9000@seldati.it>
  0 siblings, 0 replies; 6+ messages in thread

From: Fomichev Michael @ 1999-06-14 20:46 UTC (permalink / raw)
  To: Ant9000 <ant9000@seldati.it>; +Cc: pgsql-sql


On Sat, 5 Jun 1999, Ant9000 wrote:

> On Wed, 02 Jun 1999, you wrote:
> >I think that you are looking for something like:
> >
> >SELECT m.id, m.name, d.lastvisit, d.info
> >       FROM master m, detail d
> >       WHERE m.id = d.id;
> >
> >Jason
> >
> 
> No, that way I get an inner join: in my example the result would be
> 
> 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
> 
> and I'm instead looking for this output:
> 
> 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 |                                                 |
> 
 
Try this:

SELECT m.id, m.name, d.lastvisit, d.info
       FROM master m, detail d
       WHERE m.id = d.id
UNION ALL
SELECT m.id, m.name, 0, 0
       FROM master m
       WHERE not m.id=ANY(select id from detail);




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1999-06-14 20:46 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-02 18:30 OUTER JOINS in PostgreSQL ant9000@c0a111.science.unitn.it
1999-06-02 20:47 OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
1999-06-02 21:08 ` Re: [SQL] OUTER JOINs in PostgreSQL Jason Earl <jearl@box100.com>
1999-06-05 13:56   ` Re: [SQL] OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
1999-06-14 20:46     ` Re: [SQL] OUTER JOINs in PostgreSQL Fomichev Michael <fomichev@null.ru>
1999-06-06 12:06 ` Re: [SQL] OUTER JOINs in PostgreSQL Herouth Maoz <herouth@oumail.openu.ac.il>

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