pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jason Earl <jearl@box100.com>
To: ant9000@seldati.it
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] OUTER JOINs in PostgreSQL
Date: Wed, 2 Jun 1999 15:08:33 -0600
Message-ID: <199906022108.PAA13896@earlj.nesusa.com> (raw)
In-Reply-To: <99060222482601.00588@chomp>
References: <99060222482601.00588@chomp>

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





view thread (6+ messages)  latest in thread

Message-ID: <199906022108.PAA13896@earlj.nesusa.com>
Permalink:  ../199906022108.PAA13896@earlj.nesusa.com/
Also on:    postgresql.org/message-id/199906022108.PAA13896@earlj.nesusa.com

 · 

reply

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: jearl@box100.com, ant9000@seldati.it
  Subject: Re: [SQL] OUTER JOINs in PostgreSQL
  In-Reply-To: <199906022108.PAA13896@earlj.nesusa.com>
  References: <99060222482601.00588@chomp> <199906022108.PAA13896@earlj.nesusa.com>

* 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