Presto ->
select a.topicname as parent, b.topicname as child from topics as a left
join topics as b on a.id = b.pid where a.pid = 0;
parent | child
--------+------------
Foo |
Apache | PHP
XHTML |
News | World News
News | Tech News
(5 rows)
Thanks for pointing me in the right direction....
Gerard Samuel wrote:
Thanks for you help thus far.
The final goal would be to achieve results like ->
parent | child
------------+------------
Foo |
Apache | PHP
XHTML |
News | Tech News
News | World News
Im playing with the SQL to see if its possible, but any insight would
be appreciated.
Thanks again.
apz wrote:
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
