Skip site navigation (1) Skip section navigation (2)

Self Join Help

From: Gerard Samuel <gsam(at)trini0(dot)org>
To: pgsql-php(at)postgresql(dot)org
Subject: Self Join Help
Date: 2003-04-19 05:07:24
Message-ID: 3EA0D98C.8010004@trini0.org (view raw or flat)
Thread:
Lists: pgsql-php
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)


Responses

pgsql-php by date

Next:From: apzDate: 2003-04-19 06:38:37
Subject: Re: Self Join Help
Previous:From: Bernd HoffmannDate: 2003-04-18 21:54:53
Subject: Re: session_pgsql-0.6.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group