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