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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-php by date

  From Date Subject
Next Message apz 2003-04-19 06:38:37 Re: Self Join Help
Previous Message Bernd Hoffmann 2003-04-18 21:54:53 Re: session_pgsql-0.6.1