X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 028CF476085 for ; Sat, 19 Apr 2003 02:45:13 -0400 (EDT) Received: from alegro.binertia.com (unknown [216.254.174.62]) by postgresql.org (Postfix) with ESMTP id DAF45475B85 for ; Sat, 19 Apr 2003 02:45:08 -0400 (EDT) Received: from nofate.com (unknown [24.114.91.235]) by alegro.binertia.com (Postfix) with ESMTP id 16ED6C8249; Sat, 19 Apr 2003 02:53:38 -0400 (EDT) Message-ID: <3EA0EEED.1020404@nofate.com> Date: Sat, 19 Apr 2003 02:38:37 -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 To: Gerard Samuel Cc: pgsql-php@postgresql.org Subject: Re: Self Join Help References: <3EA0D98C.8010004@trini0.org> In-Reply-To: <3EA0D98C.8010004@trini0.org> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-37.6 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, RCVD_IN_NJABL,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/14 X-Sequence-Number: 1984 Gerard Samuel wrote: > 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. > 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) do you mean return also root nodes? You could just add insert into topics (id, topicname) values (0, 'root'); and then you should be getting test=# select a.topicname as parent, b.topicname as child from topics as a, topics as b where a.id = b.pid; parent | child --------+------------ root | Apache root | News root | Foo root | XHTML Apache | PHP News | Tech News News | World News or, if you dont want to add a ficticious root node you could do a left join (if you can do left self joins, dont see a reason why not, but never did it): 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 but then, your self referencing querry starts going into the idea of recursive select statements. I have little knowledge in this, MS-SQL does not have true recursive selects (you can string up bunch of left joins, but its a workaround hack). Oracle and I think db2 do support recursive selects, but only to a certain level (Oracle recurses up to 32levels I think), I wouldnt mind hearing how recursive Select would work in your case: by recursive I mean I want to select all nodes who have a specific node above the tree (be it parent/grand parent/ grand grand parent, etc). /apz, If your aim in life is nothing, you can't miss.