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 thinkon (par.parent_id = stu.parent_id and stu.stuff_id=1)
will give the join you want.