From: Ian Barwick <barwick@gmx.net>
To: "Slawek Jarosz" <Jarosz.S@ems-t.ca>, pgsql-sql@postgresql.org
Subject: Re: Optional join
Date: Sat, 16 Aug 2003 18:56:57 +0200
User-Agent: KMail/1.5.1
References: <sf3b6707.030@ems-t.ca>
In-Reply-To: <sf3b6707.030@ems-t.ca>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200308161856.57476.barwick@gmx.net>

On Thursday 14 August 2003 16:40, Slawek Jarosz wrote:
> Hi,
>
>   I trying to write a query that will join 2 tables.   Here's the concept:
> Table 1: table1, primary key pk1
> Table 2: table2, primary key pk2
>
> One of the fields (f2) in table2 contains either the primary key of table1
> or a NULL value.  So normally a pretty basic query:
>
> SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 =
> table1.pk1;
>
> BUT what I would like to do is show all records of Table 1 even if there is
> no match in Table 2.  Meaning that the reults could be table1...  
> table2.pk2
> table1...   NULL
>
> Doable?

You need an OUTER JOIN, see e.g.
http://www.postgresql.org/docs/7.3/static/tutorial-join.html

Ian Barwick
barwick@gmx.net