Skip site navigation (1) Skip section navigation (2)

Re: Having a mental block with (self) outer joins

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Having a mental block with (self) outer joins
Date: 2008-04-21 14:05:21
Message-ID: 20080421140521.GA15223@depesz.com (view raw or flat)
Thread:
Lists: 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)

In response to

Responses

pgsql-sql by date

Next:From: Thomas KellererDate: 2008-04-21 14:15:20
Subject: Re: Having a mental block with (self) outer joins
Previous:From: Thomas KellererDate: 2008-04-21 13:48:23
Subject: Having a mental block with (self) outer joins

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group