Having a mental block with (self) outer joins

Поиск
Список
Период
Сортировка
От Thomas Kellerer
Тема Having a mental block with (self) outer joins
Дата
Msg-id fui5v8$pi9$1@ger.gmane.org
обсуждение исходный текст
Ответы Re: Having a mental block with (self) outer joins  (hubert depesz lubaczewski <depesz@depesz.com>)
Список 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
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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Desc Commnad in pgsql?
Следующее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Having a mental block with (self) outer joins