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.

Derina

Which version of Oracle are you using? – Arterial

Have you heard of the keyword connect_by_root? Search for it and you'll have your answer. – Itagaki

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.

Buchbinder

Answer 2

This is what you are asking for:

SELECT     child
FROM       (
             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
           )
START WITH father IN ( SELECT  father
                       FROM     
                       (
                                   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
                        )
                       START WITH child = 2
                       CONNECT BY PRIOR father = child)
CONNECT BY PRIOR child = father
UNION
SELECT     father
FROM       (
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
          )
START WITH child IN (
SELECT     child
FROM       (
             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
           )
        START WITH child = 2
          CONNECT BY PRIOR child = father)


CONNECT BY PRIOR father = child;

This query consists in two blocks. First block extract all children of all fathers of 2 (the parameter), second block (with union to avoid duplicates) extracts all fathers of all children of 2.

Cue

Cool! Thanks. I'm going to check my answer – Cue

Thanks but i have a table with customers relationships in this format. Simple Example - 1 is father of 2 and 2 is father of 3 and 4 is father of 5. In this example i have a two trees 1,2,3 and 4,5. When i known customer 2 i need to get 1,2,3. – Derina

Is Id2 or Id1 the "father field"? Could you edit your question changing fields name for better undestanding? – Cue

thank you very much for you time! I have edited my question for better understanding. Its my customers relationship model. Unfortunately i don't have a records where father field is null. – Derina

Ok, now that you changed the example I reformulated the code. It extract what you asked. – Cue

Result is 1,2,3,4,6,7 Node =5 is missng :( – Derina

And what i need to do when i start with father node ? like 1 or 3. Thank you. – Derina

It very close... but if i start with child=4 i get 2,3,4,6,7 (without 5 and 1) :( – Derina

You asked for 2 ;). Seriously, if you want a general solution to this problem, I think you have to go procedural with PL/SQL o whatever language you prefer. IIRC, I think you have formulated this classical computer science problem: en.wikipedia.org/wiki/…. So pick up any implementation you can find of it, load data from db and once you have strongly connected components, you have the result you have only to find the SCC of an element. Of course if you can do assumptions on your model, perhaps you can avoid this algorithm. – Cue

Or simplier, you could get the result of the query I provided to you and apply again the query on all results until your set does not change. For example. If you start with 4, you get 2,3,4,6,7. Apply query again on 2,3,6,7. When you apply on 2, you see that result is 1,2,3,4,5,6,7 and this does not change any more – Cue

Thank you very much for you answers. Yes i can write a function with PL/SQL and may be it can be faster than selects on connect by statement. – Derina

Answer 3

if i understood you right , you can use the connect_by_root function as below :

select hier.child , hier.father
from 
(
  select t.* , connect_by_root(father) top_father
  from 
  (
    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
  ) t
  connect by t.child = prior t.father
) hier
where 2 in (hier.child , hier.top_father);

Stonefly