Received: from earlj.nesusa.com (fw1.nesusa.com [204.31.203.254]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA23636 for ; Wed, 2 Jun 1999 17:09:54 -0400 (EDT) (envelope-from jearl@box100.com) Received: (from earlj@localhost) by earlj.nesusa.com (8.9.3/8.9.3) id PAA13896; Wed, 2 Jun 1999 15:08:33 -0600 Date: Wed, 2 Jun 1999 15:08:33 -0600 Message-Id: <199906022108.PAA13896@earlj.nesusa.com> X-Authentication-Warning: earlj.nesusa.com: earlj set sender to jearl@box100.com using -f From: Jason Earl To: ant9000@seldati.it CC: pgsql-sql@postgreSQL.org In-reply-to: <99060222482601.00588@chomp> (message from Ant9000 on Wed, 2 Jun 1999 22:47:09 +0200) Subject: Re: [SQL] OUTER JOINs in PostgreSQL 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