From: Gerard Samuel <gsam@trini0.org>
To: apz <apz@nofate.com>
Cc: pgsql-php@postgresql.org
Subject: Re: Self Join Help
Date: Sat, 19 Apr 2003 10:01:18 -0400
Message-ID: <3EA156AE.7090400@trini0.org> (raw)
In-Reply-To: <3EA0F16E.2040408@nofate.com>
References: <3EA0D98C.8010004@trini0.org>
<3EA0EEED.1020404@nofate.com>
<3EA0F16E.2040408@nofate.com>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: gsam@trini0.org, apz@nofate.com
Subject: Re: Self Join Help
In-Reply-To: <3EA156AE.7090400@trini0.org>
References: <3EA0D98C.8010004@trini0.org> <3EA0EEED.1020404@nofate.com> <3EA0F16E.2040408@nofate.com> <3EA156AE.7090400@trini0.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox