Search code examples
sqloracle-databasehierarchy

Subtree selection with connect by

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?

Solution

  • I went with this combo of ideas: - thanks to all - I have upvoted

    select id, depth, depth - MIN(depth) OVER()+1 lvl 
from (
SELECT parent_id AS id, depth
FROM   (
  SELECT parent_id, 1-LEVEL AS depth
  FROM   table_name
  START WITH child_id = 12
  CONNECT BY
       PRIOR parent_id = child_id
  ORDER BY LEVEL DESC
)
UNION ALL
SELECT child_id, depth
FROM   (
  SELECT child_id, LEVEL AS depth
  FROM   table_name
  START WITH child_id = 12
  CONNECT BY
         parent_id = PRIOR child_id
  ORDER SIBLINGS BY child_id
));