Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 869A72E004D for ; Mon, 21 Apr 2008 11:05:28 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65447-08 for ; Mon, 21 Apr 2008 11:05:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from depesz.com (depesz.com [88.198.37.40]) by developer.postgresql.org (Postfix) with ESMTP id 512D92E0055 for ; Mon, 21 Apr 2008 11:05:23 -0300 (ADT) Received: from depesz by depesz.com with local (Exim 4.63) (envelope-from ) id 1Jnwe1-0006SO-HY; Mon, 21 Apr 2008 16:05:21 +0200 Date: Mon, 21 Apr 2008 16:05:21 +0200 From: hubert depesz lubaczewski To: Thomas Kellerer Cc: pgsql-sql@postgresql.org Subject: Re: Having a mental block with (self) outer joins Message-ID: <20080421140521.GA15223@depesz.com> Reply-To: depesz@depesz.com References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/124 X-Sequence-Number: 30663 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)