Thread: Having a mental block with (self) outer joins

Hi, 

I'm playing around with putting a hierarchy of items into the database. But for some reason I'm having a mental block
understandingthe following:
 

I have a table category with id and parent_id implementing the typical adjacency model.

To get the first two levels of the hierarchy I use:

SELECT t1.name as lev1, t2.name as lev2
FROM category t1
LEFT JOIN category t2 ON t2.parent = t1.id
WHERE t1.name = 'ROOT'
;

Now what I don't understand is that the root node (which has a NULL parent_id) is not selected. 

My understanding from the outer join is that it would return all items from the "left" tables regardless whether they
havea corresponding row in the "right" table. 
 

So given the data

name, id, parent_id
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. 

I'm sure I'm missing something very obvious, but what?

Thanks in advance 
Thomas

On Mon, Apr 21, 2008 at 03:48:23PM +0200, Thomas Kellerer wrote:
> name, id, parent_id
> 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.

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

depesz

-- 
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

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
wastrying 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