pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: apz <apz@nofate.com>
To: Gerard Samuel <gsam@trini0.org>
Cc: pgsql-php@postgresql.org
Subject: Re: Self Join Help
Date: Sat, 19 Apr 2003 02:38:37 -0400
Message-ID: <3EA0EEED.1020404@nofate.com> (raw)
In-Reply-To: <3EA0D98C.8010004@trini0.org>
References: <3EA0D98C.8010004@trini0.org>

Gerard Samuel wrote:
> Im trying to figure out Self Joins with PostgreSQL.  The output of the 
> second SQL is correct, because of the where a.id = b.pid,
> but I would like to return all rows that are part of the tree.
> test=# select * from topics;
>        id        |       pid        | topicname
> ------------------+------------------+------------
> AFAdDFoAPNX6wKbr | 0                | Foo
> AFAdDFoAPgTi9tAE | 0                | Apache
> AFAdDFoAPgTjCa4V | AFAdDFoAPgTi9tAE | PHP
> AFAdDFoAPlv1ENRn | 0                | XHTML
> AFAdDFoAPoSEWZaq | 0                | News
> AFAdDFoAPoSEaRPV | AFAdDFoAPoSEWZaq | World News
> AFAdDFoAPoSEee5_ | AFAdDFoAPoSEWZaq | Tech News
> (7 rows)
> 
> test=# select a.topicname as parent, b.topicname as child from topics as 
> a, topics as b where a.id = b.pid;
> parent |   child
> --------+------------
> Apache | PHP
> News   | Tech News
> News   | World News
> (3 rows)

do you mean return also root nodes? You could just add

insert into topics (id, topicname) values (0, 'root');

and then you should be getting

test=# select a.topicname as parent, b.topicname as child from topics as
  a, topics as b where a.id = b.pid;

  parent |   child
--------+------------
  root   | Apache
  root   | News
  root   | Foo
  root   | XHTML
  Apache | PHP
  News   | Tech News
  News   | World News


or, if you dont want to add a ficticious root node you could do a left 
join (if you can do left self joins, dont see a reason why not, but 
never did it):

test=# select a.topicname as parent, b.topicname as child from topics as 
a left join topics as b on a.id = b.pid;

  parent |   child
--------+------------
  XHTML  | null
  Foo    | null
  Apache | PHP
  News   | Tech News
  News   | World News


but then, your self referencing querry starts going into the idea of 
recursive select statements. I have little knowledge in this, MS-SQL 
does not have true recursive selects (you can string up bunch of left 
joins, but its a workaround hack). Oracle and I think db2 do support 
recursive selects, but only to a certain level (Oracle recurses up to 
32levels I think), I wouldnt mind hearing how recursive Select would 
work in your case:

	by recursive I mean I want to select all nodes who have a
	specific node above the tree (be it parent/grand parent/
	grand grand parent, etc).


/apz,   If your aim in life is nothing, you can't miss.




view thread (5+ messages)  latest in thread

Message-ID: <3EA0EEED.1020404@nofate.com>
Permalink:  ../3EA0EEED.1020404@nofate.com/
Also on:    postgresql.org/message-id/3EA0EEED.1020404@nofate.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: apz@nofate.com, gsam@trini0.org
  Subject: Re: Self Join Help
  In-Reply-To: <3EA0EEED.1020404@nofate.com>
  References: <3EA0D98C.8010004@trini0.org> <3EA0EEED.1020404@nofate.com>

* 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