X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 67BE947637F for ; Sat, 19 Apr 2003 02:55:50 -0400 (EDT) Received: from alegro.binertia.com (unknown [216.254.174.62]) by postgresql.org (Postfix) with ESMTP id A9D7247637C for ; Sat, 19 Apr 2003 02:55:49 -0400 (EDT) Received: from nofate.com (unknown [24.114.91.235]) by alegro.binertia.com (Postfix) with ESMTP id 9244DC8249; Sat, 19 Apr 2003 03:04:19 -0400 (EDT) Message-ID: <3EA0F16E.2040408@nofate.com> Date: Sat, 19 Apr 2003 02:49:18 -0400 From: apz User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4a) Gecko/20030401 X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: Gerard Samuel , pgsql-php@postgresql.org Subject: Re: Self Join Help References: <3EA0D98C.8010004@trini0.org> <3EA0EEED.1020404@nofate.com> In-Reply-To: <3EA0EEED.1020404@nofate.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-21.4 required=5.0 tests=BAYES_01,IN_REP_TO,RCVD_IN_NJABL,REFERENCES, 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/15 X-Sequence-Number: 1985 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.