X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 28EDC475AE4 for ; Sat, 19 Apr 2003 10:01:15 -0400 (EDT) Received: from smtp-out.comcast.net (smtp-out.comcast.net [24.153.64.109]) by postgresql.org (Postfix) with ESMTP id 3F65A4758C9 for ; Sat, 19 Apr 2003 10:01:14 -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 <0HDL00H8VGA6BC@mtaout11.icomcast.net> for pgsql-php@postgresql.org; Sat, 19 Apr 2003 10:01:18 -0400 (EDT) Received: from trini0.org (gladiator.trini0.org [192.168.0.3]) by hivemind.trini0.org (Postfix) with ESMTP id 27A394A4; Sat, 19 Apr 2003 10:01:18 -0400 (EDT) Date: Sat, 19 Apr 2003 10:01:18 -0400 From: Gerard Samuel Subject: Re: Self Join Help In-reply-to: <3EA0F16E.2040408@nofate.com> To: apz Cc: pgsql-php@postgresql.org Message-id: <3EA156AE.7090400@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> X-Spam-Status: No, hits=-32.3 required=5.0 tests=BAYES_01,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/17 X-Sequence-Number: 1987 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 > >