Thread: Self Join Help
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.
i.e.
Foo
Apache
- PHP
XHTML
News
- World News
- Tech News
Any help would be appreciated.
Thanks.
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)
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.
this is second time I post to a forum, and second time I correct myself... ugh, I should delay posting by 15 minutes, or stop re-reading my emails after posting... ;D anyways: apz wrote: > 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 this actually should return: 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 PHP | null Tech News | null World News | null the querry with left join should return child=null if a node is a leaf. so XHTML and PHP return child as null because neither have any nodes underneath. this also should mean that topicname should not allow null values, not to confuse ourselves further on. so two ways are: - add one node which is always root, use your querry - use left join, when no child then child returns as null /apz, You can always tell luck from ability by its duration.
Thanks for you help thus far. The final goal would be to achieve results like -> parent | child ------------+------------ Foo | Apache | PHP XHTML | News | Tech News News | World News Im playing with the SQL to see if its possible, but any insight would be appreciated. Thanks again. apz wrote: > this is second time I post to a forum, and second time I correct > myself... ugh, I should delay posting by 15 minutes, or stop > re-reading my emails after posting... ;D > > anyways: > > apz wrote: > >> 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 > > > this actually should return: > > > 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 > PHP | null > Tech News | null > World News | null > > > the querry with left join should return child=null if a node is a leaf. > so XHTML and PHP return child as null because neither have any nodes > underneath. > this also should mean that topicname should not allow null values, not > to confuse ourselves further on. > > so two ways are: > - add one node which is always root, use your querry > - use left join, when no child then child returns as null > > > > /apz, You can always tell luck from ability by its duration. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
Presto -> select a.topicname as parent, b.topicname as child from topics as a left join topics as b on a.id = b.pid where a.pid = 0; parent | child --------+------------ Foo | Apache | PHP XHTML | News | World News News | Tech News (5 rows) Thanks for pointing me in the right direction.... Gerard Samuel wrote: > Thanks for you help thus far. > The final goal would be to achieve results like -> > parent | child > ------------+------------ > Foo | > Apache | PHP > XHTML | > News | Tech News > News | World News > > Im playing with the SQL to see if its possible, but any insight would > be appreciated. > Thanks again. > > apz wrote: > >> this is second time I post to a forum, and second time I correct >> myself... ugh, I should delay posting by 15 minutes, or stop >> re-reading my emails after posting... ;D >> >> anyways: >> >> apz wrote: >> >>> 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 >> >> >> >> this actually should return: >> >> >> 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 >> PHP | null >> Tech News | null >> World News | null >> >> >> the querry with left join should return child=null if a node is a leaf. >> so XHTML and PHP return child as null because neither have any nodes >> underneath. >> this also should mean that topicname should not allow null values, >> not to confuse ourselves further on. >> >> so two ways are: >> - add one node which is always root, use your querry >> - use left join, when no child then child returns as null >> >> >> >> /apz, You can always tell luck from ability by its duration. >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >