Обсуждение: LEFT OUTER JOIN problem
Hello, I have this problem (maybe only in my head ;o)): table1: ------- id | name --------- 1 | 'blabla' 2 | 'arrrgh' table2: ------- id | table1_id | name --------------------- 1 | 1 | 'hello' table3: ------- id | table2_id | name --------------------- SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.table1_id) LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); This select return me only one row: 1,'blabla',1,1,'hello',NULL,NULL,NULL But I think it may return two rows: 1,'blabla',1,1,'hello',NULL,NULL,NULL 2,'arrrgh',NULL,NULL,NULL,NULL,NULL,NULL Where is the problem (in my head or in postgres)? Do you know what select statement return me what I want? Please reply to my address (xfinstrl@fi.muni.cz) as I'm not in list. Thanks Luf
Ludek, > I have this problem (maybe only in my head ;o)): Yup. Or it's a language problem. There's a fair Czech community of PgSQL users, so hopefully you can get in touch with some of them. (Your English is better than any of my 2nd languages -- it's just that techincal docs are hard enough to understand in one's native language!) > SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = > table2.table1_id) > LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); > > This select return me only one row: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > > But I think it may return two rows: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > 2,'arrrgh',NULL,NULL,NULL,NULL,NULL,NULL Yes, it will. The query you want is: SELECT * FROM table1 JOIN table2 ON (table1.id =table2.table1_id) LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Fri, 21 Sep 2001, Ludek Finstrle wrote: > Hello, > > I have this problem (maybe only in my head ;o)): > > table1: > ------- > id | name > --------- > 1 | 'blabla' > 2 | 'arrrgh' > > table2: > ------- > id | table1_id | name > --------------------- > 1 | 1 | 'hello' > > table3: > ------- > id | table2_id | name > --------------------- > > SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.table1_id) > LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); > > This select return me only one row: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > > But I think it may return two rows: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > 2,'arrrgh',NULL,NULL,NULL,NULL,NULL,NULL > > Where is the problem (in my head or in postgres)? Do you know what select > statement return me what I want? What version are you using? On 7.2 devel I get the two row result.