Skip site navigation (1) Skip section navigation (2)

where clause on a left outer join

From: Cris Carampa <cris119(at)operamail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: where clause on a left outer join
Date: 2004-09-22 09:08:22
Message-ID: cirfem$75c$1@floppy.pyrenet.fr (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)operamail(dot)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

Responses

pgsql-sql by date

Next:From: S.SreejithDate: 2004-09-22 11:11:49
Subject: Doubt
Previous:From: Tom LaneDate: 2004-09-21 22:29:10
Subject: Re: raise is not working

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group