Couple of ways to do it. One is to use the hierarchical query patch
that mimics Oracle's CONNECT BY
syntax at
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?
recipe_id=19490.
Another way is to use a nested set model, described at
http://www.geocrawler.com/archives/3/6/2001/10/0/6961775/,
which is an extract from the book SQL For Smarties by Joe Celko.
On Feb 2, 2004, at 4:54 PM, CSN wrote:
> I have a table with these columns:
>
> id, node, parent_node_id
>
> The top-most nodes would have a parent_node_id of
> NULL. Is it possible to get a node, and all its parent
> nodes, in a single query?
>
> For example, a node might be:
>
> books > computers > databases > oss > postgres
>
> and the rows fetched would be:
>
> 1,books,NULL
> 2,computers,1
> 3,databases,2
> 4,oss,3
> 5,postgres,4
>
> TIA,
> CSN
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free web site building tool. Try it!
> http://webhosting.yahoo.com/ps/sb/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>
--------------------
Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com
