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)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Self Join Help

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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Self Join Help

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

Re: Self Join Help

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

Re: Self Join Help

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
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org