[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] where clause on a left outer join
From:       Stephan Szabo <sszabo () megazone ! bigpanda ! com>
Date:       2004-09-27 0:21:13
Message-ID: 20040926171842.A46859 () megazone ! bigpanda ! com
[Download RAW message or body]


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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
[prev in list] [next in list] [prev in thread] [next in thread]