From: Cris Carampa <cris119@operamail.com>
Subject: where clause on a left outer join
Date: Wed, 22 Sep 2004 11:08:22 +0200
Organization: PYRENET Midi-pyrenees Provider
Lines: 99
Message-ID: <cirfem$75c$1@floppy.pyrenet.fr>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040114
To: pgsql-sql@postgresql.org

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
;

Gives the following result:

  parent_id | stuff_data
-----------+------------
          1 | xxx
(1 row)

But this is not what I want.

The following query:

select
   par.parent_id,
   stu.stuff_data
from
   parent par
   left outer join
   (
     select
       *
     from
       stuff
     where
       stuff_id = 1
   ) stu
   on (
     par.parent_id = stu.parent_id
   )
;

Gives the following result:

  parent_id | stuff_data
-----------+------------
          1 | xxx
          2 |
          3 |
(3 rows)

Which is exacly what I want.

I'm wondering whether there is another way to get this result, without 
using the online view.

Thank you. Kind regards,

-- 
Cris Carampa (spamto:cris119@operamail.com)
I got some John Coltrane on the stereo baby make it feel all right
I got some fine wine in the freezer mama I know what you like
I said a man works hard all day he can do what he wants to at night