Oracle Recursive Select at Wednesday, November 12, 2008

If you have a table that represents a hierarchy you can do a recursive select as follows.

SELECT * FROM my_table
CONNECT BY PRIOR child_id = parent_id

If you want select a subtree you can use 'starts with'. Replace x with the id of your starting node.
The x part can also be a subselect in an IN clause.

SELECT * from my_table
CONNECT BY PRIOR child_id = parent_id 
START WITH parent_id = x

There also a column called 'level' which you can use to find the level of nesting.

You can 'walk' up the tree from a child node like this.

SELECT * from my_table
CONNECT BY PRIOR  parent_id = child_id
START WITH child_id = x