Edit Question - KnowledgeHub

Title Body

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) s

Tags (comma-separated) Cancel