Create recursive query for tree like data set

Question

| Col1      | Col2  | Col3      | Col4  | Col5
--------------------------------------------------
| B1        | A1    | somedata  | data  | somedata
| B2        | A1    | somedata  | data  | somedata
| B3        | A1    | null      | data  | somedata
| C1        | B1    | null      | data  | somedata
| C2        | B1    | 1         | data  | somedata
| D1        | C1    | 1         | data  | somedata
| D2        | C1    | 4         | data  | somedata
| D3        | C1    | 4         | data  | somedata
| Z1        | K1    | A         | B     | C
| Z1        | K2    | A         | B     | C



                    A1
                /       \          \ 
              B1        B2          B3
            /   \
          C1    C2
    /   \   \
    D1   D2  D3

I have the following table structure. Col1 holds children of Col2. Other columns have other related data. The table holds data for a tree like structure. Is it possible to create a query that'll provide all the children for A1. In this case it'll be B1 B2 B3 C1 C2 D1 D2 D3

I'm only familiar with simple joins and can't figure out how to query the data. Any help would be appreciated.

Take a look at the Oracle CONNECTY BY clause – OldProgrammer Mar 25 '19 at 21:11 — OldProgrammer, Mar 25 '19 at 21:11

score 0 · Accepted Answer · answered Mar 25 '19 at 21:13

Shouldn't be too complicated:

SQL> with test (col1, col2) as
  2    (select 'B1', 'A1' from dual union all
  3     select 'B2', 'A1' from dual union all
  4     select 'B3', 'A1' from dual union all
  5     select 'C1', 'B1' from dual union all
  6     select 'C2', 'B1' from dual union all
  7     select 'D1', 'C1' from dual union all
  8     select 'D2', 'C1' from dual union all
  9     select 'D3', 'C1' from dual union all
 10     select 'Z1', 'K1' from dual union all
 11     select 'Z1', 'K2' from dual
 12    )
 13  select col1
 14  from test
 15  start with col2 = 'A1'
 16  connect by prior col1 = col2
 17  order by col1;

CO
--
B1
B2
B3
C1
C2
D1
D2
D3

8 rows selected.

SQL>

Create recursive query for tree like data set

1 Answers1