Skip site navigation (1) Skip section navigation (2)

Having a mental block with (self) outer joins

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Having a mental block with (self) outer joins
Date: 2008-04-21 13:48:23
Message-ID: fui5v8$pi9$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-sql
Hi, 

I'm playing around with putting a hierarchy of items into the database. But for some reason I'm having a mental block understanding the 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 have a 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


Responses

pgsql-sql by date

Next:From: hubert depesz lubaczewskiDate: 2008-04-21 14:05:21
Subject: Re: Having a mental block with (self) outer joins
Previous:From: Alvaro HerreraDate: 2008-04-21 13:36:26
Subject: Re: Desc Commnad in pgsql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group