Re: [SQL] OUTER JOINs in PostgreSQL - Mailing list 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  |
WedJun 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'
ifthey 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,NULLAS 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