X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 63E0E475AE4 for ; Sat, 19 Apr 2003 12:25:43 -0400 (EDT) Received: from smtp-out.comcast.net (smtp-out.comcast.net [24.153.64.109]) by postgresql.org (Postfix) with ESMTP id A894F4758C9 for ; Sat, 19 Apr 2003 12:25:42 -0400 (EDT) Received: from hivemind.trini0.org (bgp626680bgs.brick201.nj.comcast.net [68.39.132.244]) by mtaout11.icomcast.net (iPlanet Messaging Server 5.2 HotFix 1.14 (built Mar 18 2003)) with ESMTP id <0HDL00IUQMZ0CJ@mtaout11.icomcast.net> for pgsql-php@postgresql.org; Sat, 19 Apr 2003 12:25:48 -0400 (EDT) Received: from trini0.org (gladiator.trini0.org [192.168.0.3]) by hivemind.trini0.org (Postfix) with ESMTP id 7A9A34A4; Sat, 19 Apr 2003 12:25:44 -0400 (EDT) Date: Sat, 19 Apr 2003 12:25:44 -0400 From: Gerard Samuel Subject: Re: Self Join Help In-reply-to: <3EA156AE.7090400@trini0.org> To: apz Cc: pgsql-php@postgresql.org Message-id: <3EA17888.1030608@trini0.org> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT X-Accept-Language: en-us, en, th User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.3) Gecko/20030417 References: <3EA0D98C.8010004@trini0.org> <3EA0EEED.1020404@nofate.com> <3EA0F16E.2040408@nofate.com> <3EA156AE.7090400@trini0.org> X-Spam-Status: No, hits=-38.8 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MOZILLA_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/18 X-Sequence-Number: 1988 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 > >