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