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
