Subtree selection with connect by

0
votes

I have some hierarchic data, similar to this diagram enter image description here

this is done for example in a table with a parent and child id

link_table
--------------
parent_id
child_id

for simplicity, the id's (sort of) from above would be like this:

parent_id    child_id
---------------------
1            11
1            12
12           121
121          1211
12           122
122          1221
122          1222
2            21

my challenge is this:

Given a selected node (122) - I need to return the tree that contains the (non cycling) root path (1 and 12 - but not 11), the selected item (122) and all further children (1221, 1222) and nothing from other trees (the original parent is a null for all trees)

I can write a normal connect by to start with the selected id and get the 'child tree'

and I can write a connect by to go upwards to the connect_by_root.

my question is: how do I combine these into one statement that returns a nice LEVEL and the nodes in the highlighted tree?

sqloraclehierarchy