Date: Sun, 26 Sep 2004 17:21:13 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Cris Carampa <cris119@operamail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: where clause on a left outer join
In-Reply-To: <cirfem$75c$1@floppy.pyrenet.fr>
Message-ID: <20040926171842.A46859@megazone.bigpanda.com>
References: <cirfem$75c$1@floppy.pyrenet.fr>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 22 Sep 2004, Cris Carampa wrote:

> Hello, let's suppose I have the following tables:
>
> create table parent (
>    parent_id numeric primary key,
>    parent_data text
> ) ;
>
> create table stuff (
>    stuff_id numeric primary key,
>    parent_id numeric references parent,
>    stuff_data text
> ) ;
>
> And the following data:
>
> crisdb=> select * from parent;
>   parent_id | parent_data
> -----------+-------------
>           1 | aaa
>           2 | bbb
>           3 | ccc
> (3 rows)
>
> crisdb=> select * from stuff;
>   stuff_id | parent_id | staff_data
> ----------+-----------+------------
>          1 |         1 | xxx
>          2 |         1 | yyy
>          3 |         1 | zzz
> (3 rows)
>
> I wish to write a query that returns all rows from "parent" and, beside
> of them, staff data with stuff_id=1 if available, otherwise null.
>
> The following query:
>
> select
>    par.parent_id,
>    stu.stuff_data
> from
>    parent par left outer join stuff stu
>    on (
>      par.parent_id = stu.parent_id
>    )
> where
>    stu.stuff_id = 1
> ;

I think
 on (par.parent_id = stu.parent_id and stu.stuff_id=1)
will give the join you want.