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
---------------------------(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
