X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6684C475B85 for ; Sat, 19 Apr 2003 01:07:26 -0400 (EDT) Received: from smtp-out.comcast.net (smtp-out.comcast.net [24.153.64.115]) by postgresql.org (Postfix) with ESMTP id 88E404758C9 for ; Sat, 19 Apr 2003 01:07:25 -0400 (EDT) Received: from hivemind.trini0.org (bgp626680bgs.brick201.nj.comcast.net [68.39.132.244]) by mtaout09.icomcast.net (iPlanet Messaging Server 5.2 HotFix 1.14 (built Mar 18 2003)) with ESMTP id <0HDK0066IRKCXR@mtaout09.icomcast.net> for pgsql-php@postgresql.org; Sat, 19 Apr 2003 01:07:25 -0400 (EDT) Received: from trini0.org (gladiator.trini0.org [192.168.0.3]) by hivemind.trini0.org (Postfix) with ESMTP id B328B3A6 for ; Sat, 19 Apr 2003 01:07:24 -0400 (EDT) Date: Sat, 19 Apr 2003 01:07:24 -0400 From: Gerard Samuel Subject: Self Join Help To: pgsql-php@postgresql.org Message-id: <3EA0D98C.8010004@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 X-Spam-Status: No, hits=-12.3 required=5.0 tests=BAYES_01,UPPERCASE_25_50,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/13 X-Sequence-Number: 1983 Im trying to figure out Self Joins with PostgreSQL. The output of the second SQL is correct, because of the where a.id = b.pid, but I would like to return all rows that are part of the tree. i.e. Foo Apache - PHP XHTML News - World News - Tech News Any help would be appreciated. Thanks. test=# select * from topics; id | pid | topicname ------------------+------------------+------------ AFAdDFoAPNX6wKbr | 0 | Foo AFAdDFoAPgTi9tAE | 0 | Apache AFAdDFoAPgTjCa4V | AFAdDFoAPgTi9tAE | PHP AFAdDFoAPlv1ENRn | 0 | XHTML AFAdDFoAPoSEWZaq | 0 | News AFAdDFoAPoSEaRPV | AFAdDFoAPoSEWZaq | World News AFAdDFoAPoSEee5_ | AFAdDFoAPoSEWZaq | Tech News (7 rows) test=# select a.topicname as parent, b.topicname as child from topics as a, topics as b where a.id = b.pid; parent | child --------+------------ Apache | PHP News | Tech News News | World News (3 rows)