Re: where clause on a left outer join

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.