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
|
Список | 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 по дате отправления: