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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | S.Sreejith | 2004-09-22 11:11:49 | Doubt |
Previous Message | Tom Lane | 2004-09-21 22:29:10 | Re: raise is not working |