To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Having a mental block with (self) outer joins
Date: Mon, 21 Apr 2008 16:15:20 +0200
Lines: 34
Message-ID: <fui7ho$12e$1@ger.gmane.org>
References: <fui5v8$pi9$1@ger.gmane.org> <20080421140521.GA15223@depesz.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.12) Gecko/20080213 Thunderbird/2.0.0.12 Mnenhy/0.7.5.666
In-Reply-To: <20080421140521.GA15223@depesz.com>

hubert depesz lubaczewski, 21.04.2008 16:05:
>> ROOT, 1, NULL
>> CHILD1, 2, 1
>> CHILD2, 3, 1
>>
>> I would have expected the following result:
>>
>> ROOT, NULL
>> ROOT, CHILD1
>> ROOT, CHILD2
>>
>> but the row with (ROOT,NULL) is not returned. 
> 
> why would you expect it?
> the columns are: parent and child (on your output).
> you dont have any row that has *parent_id = 1* and id = NULL.

Ah, of course that's where my mental block was ;)
Thanks for the quick response

> you can get this output though:
> 
> NULL, ROOT
> ROOT, CHILD1
> ROOT, CHILD2
> 
> with this query:
> 
> select p.name as parent, c.name as child from category c left outer join category p on c.parent_id = p.id

If the table contains more levels (i.e. child1 being the parent of another item) this bring others back as well. And I was trying to retrieve the full path for each item (I do know the max. number of levels)

Cheers, and thanks a lot for the quick response
Thomas