pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHaving a mental block with (self) outer joins
3+ messages / 2 participants
[nested] [flat]
* Having a mental block with (self) outer joins
@ 2008-04-21 13:48 Thomas Kellerer <spam_eater@gmx.net>
2008-04-21 14:05 ` Re: Having a mental block with (self) outer joins hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Kellerer @ 2008-04-21 13:48 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Having a mental block with (self) outer joins
2008-04-21 13:48 Having a mental block with (self) outer joins Thomas Kellerer <spam_eater@gmx.net>
@ 2008-04-21 14:05 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-04-21 14:15 ` Re: Having a mental block with (self) outer joins Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2008-04-21 14:05 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
On Mon, Apr 21, 2008 at 03:48:23PM +0200, Thomas Kellerer wrote:
> 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.
why would you expect it?
the columns are: parent and child (on your output).
you dont have any row that has *parent_id = 1* and id = NULL.
you can get this output though:
NULL, ROOT
ROOT, CHILD1
ROOT, CHILD2
with this query:
select p.name as parent, c.name as child from category c left outer join category p on c.parent_id = p.id
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Having a mental block with (self) outer joins
2008-04-21 13:48 Having a mental block with (self) outer joins Thomas Kellerer <spam_eater@gmx.net>
2008-04-21 14:05 ` Re: Having a mental block with (self) outer joins hubert depesz lubaczewski <depesz@depesz.com>
@ 2008-04-21 14:15 ` Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 3+ messages in thread
From: Thomas Kellerer @ 2008-04-21 14:15 UTC (permalink / raw)
To: pgsql-sql
hubert depesz lubaczewski, 21.04.2008 16:05:
>> 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.
>
> why would you expect it?
> the columns are: parent and child (on your output).
> you dont have any row that has *parent_id = 1* and id = NULL.
Ah, of course that's where my mental block was ;)
Thanks for the quick response
> you can get this output though:
>
> NULL, ROOT
> ROOT, CHILD1
> ROOT, CHILD2
>
> with this query:
>
> select p.name as parent, c.name as child from category c left outer join category p on c.parent_id = p.id
If the table contains more levels (i.e. child1 being the parent of another item) this bring others back as well. And I was trying to retrieve the full path for each item (I do know the max. number of levels)
Cheers, and thanks a lot for the quick response
Thomas
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-04-21 14:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-21 13:48 Having a mental block with (self) outer joins Thomas Kellerer <spam_eater@gmx.net>
2008-04-21 14:05 ` hubert depesz lubaczewski <depesz@depesz.com>
2008-04-21 14:15 ` Thomas Kellerer <spam_eater@gmx.net>
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