pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 12:25:44 -0400
Message-ID: <3EA17888.1030608@trini0.org> (raw)
In-Reply-To: <3EA156AE.7090400@trini0.org>
References: <3EA0D98C.8010004@trini0.org>
	<3EA0EEED.1020404@nofate.com>
	<3EA0F16E.2040408@nofate.com>
	<3EA156AE.7090400@trini0.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
>
>




view thread (5+ messages)

Message-ID: <3EA17888.1030608@trini0.org>
Permalink:  ../3EA17888.1030608@trini0.org/
Also on:    postgresql.org/message-id/3EA17888.1030608@trini0.org

 · 

reply

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: <3EA17888.1030608@trini0.org>
  References: <3EA0D98C.8010004@trini0.org> <3EA0EEED.1020404@nofate.com> <3EA0F16E.2040408@nofate.com> <3EA156AE.7090400@trini0.org> <3EA17888.1030608@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