where clause on a left outer join
От | Cris Carampa |
---|---|
Тема | where clause on a left outer join |
Дата | |
Msg-id | cirfem$75c$1@floppy.pyrenet.fr обсуждение исходный текст |
Ответы |
Re: where clause on a left outer join
|
Список | 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@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
В списке pgsql-sql по дате отправления: