From: | apz <apz(at)nofate(dot)com> |
---|---|
To: | Gerard Samuel <gsam(at)trini0(dot)org> |
Cc: | pgsql-php(at)postgresql(dot)org |
Subject: | Re: Self Join Help |
Date: | 2003-04-19 06:38:37 |
Message-ID: | 3EA0EEED.1020404@nofate.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-php |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | apz | 2003-04-19 06:49:18 | Re: Self Join Help |
Previous Message | Gerard Samuel | 2003-04-19 05:07:24 | Self Join Help |