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