From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Having a mental block with (self) outer joins
Date: Mon, 21 Apr 2008 15:48:23 +0200
Message-ID: <fui5v8$pi9$1@ger.gmane.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Having a mental block with (self) outer joins
In-Reply-To: <fui5v8$pi9$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox