pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
LEFT OUTER JOIN problem
3+ messages / 3 participants
[nested] [flat]

* LEFT OUTER JOIN problem
@ 2001-09-21 13:32  Ludek Finstrle <xfinstrl@informatics.muni.cz>
  0 siblings, 2 replies; 3+ messages in thread

From: Ludek Finstrle @ 2001-09-21 13:32 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: LEFT OUTER JOIN problem
@ 2001-09-27 17:49  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Ludek Finstrle <xfinstrl@informatics.muni.cz>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-09-27 17:49 UTC (permalink / raw)
  To: xfinstrl@fi.muni.cz; +Cc: pgsql-sql


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.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: LEFT OUTER JOIN problem
@ 2001-09-27 18:22  Josh Berkus <josh@agliodbs.com>
  parent: Ludek Finstrle <xfinstrl@informatics.muni.cz>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2001-09-27 18:22 UTC (permalink / raw)
  To: Ludek Finstrle <xfinstrl@informatics.muni.cz>; pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-09-27 18:22 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-21 13:32 LEFT OUTER JOIN problem Ludek Finstrle <xfinstrl@informatics.muni.cz>
2001-09-27 17:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-09-27 18:22 ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox