How to get all values in hierarchy linked to some value using Oracle CONNECT BY

Question

Relationship model is

1   3   
 \ / \
  2   4
   \
    7   5     8
     \ /     /
      6     9

Table is :

select 2 child, 1 father from dual
union all
select 2 child, 3 father from dual
union all
select 4 child, 3 father from dual
union all
select 7 child, 2 father from dual
union all
select 6 child, 5 father from dual
union all
select 6 child, 7 father from dual
union all
select 9 child, 8 father from dual

How can I get all values linked with value CHILD or FATHER = 2 ?

It must be

1,2,3,4,5,6,7

and not

8,9

since it's not linked to value 2.

How to achieve this by using CONNECT BY statement? Thank you.

p.s. this solution is very close to me but not working for my model:

Find all nodes in an adjacency list model with oracle connect by

DB version - 10.2.0.5.0

model-with-oracle-connect-by

So, aproximately strategy may be like this (for example start with node=7):

Step 1 (direction = up)

select t1.father,connect_by_root father as root,connect_by_isleaf from 
(my_table) t1
start with father=7
connect by prior father = child 

Result is 7,2,1,3 where 1,3 is high level root (isleaf=1)

Step 2 (get route for 1,3 direction=down)

select t1.child,connect_by_root father as root,connect_by_isleaf from 
(my_table) t1
start with father=1
connect by father = prior child 

Result is 2,7,6 where 6 is low level root (isleaf=1)

select t1.child,connect_by_root father as root,connect_by_isleaf from 
(my_table) t1
start with father=3
connect by father = prior child

result is 2,7,6,4 where 6,4 is low level root (isleaf=1)

Step 3 (get route for 6,4 direction = up)

select t1.father,connect_by_root father as root,connect_by_isleaf from 
(my_table) t1
start with child=6
connect by prior father = child 

Result is 5,7,2,1,3 where 5,1,3 is high level root (isleaf=1) Is this result i found node=5

Then i must change direction to down.. then again up.. then again down..

But how to union all this steps in one select ? It very hard for beginer . Help me please.

Answer 1

For your output you don't need your graph to be directed, so add the reverse links to all existing links. That's what I do in the subquery 'bi'. Then you use a nocyle connect by query.

    with h as (
                     SELECT 2 child, 1 father FROM dual
                     UNION ALL
                     SELECT 2 child, 3 father FROM dual
                     UNION ALL
                     SELECT 4 child, 3 father FROM dual
                     UNION ALL
                     SELECT 7 child, 2 father FROM dual
                     UNION ALL
                     SELECT 6 child, 5 father FROM dual
                     UNION ALL
                     SELECT 6 child, 7 father FROM dual
                     UNION ALL
                     SELECT 9 child, 8 father FROM dual
            ),
    bi as (select * from h union all select father , child from h )     
    select distinct father from bi
    start with child = 2
    connect by nocycle
    prior father = child

I'm using the 'with' notation for the query for better readability.