Skip to Main Content
  • Questions
  • How to find child or parent from data with only ID and Level

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gots.

Asked: November 30, 2020 - 2:42 pm UTC

Last updated: December 04, 2020 - 3:16 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have this data

select 1 LineNumber, 1 mylevel, 10 id from dual
union all
select 2 , 2 , 11 from dual
union all
select 3 , 3 , 13 from dual
union all
select 4 , 3 , 14 from dual
union all
select 5 , 4 , 15 from dual
union all
select 6 , 2 , 20 from dual
union all
select 7 , 2 , 30 from dual
union all
select 8 , 3 , 31 from dual
union all
select 9 , 4 , 33 from dual
union all
select 10 , 3 , 32 from dual
union all
select 11 , 3 , 34 from dual
union all
select 12 , 4 , 35 from dual
union all
select 13 , 5 , 36 from dual


I'm look for a way to get
- the child ids recursively of the id = 30.
- the parent id of the id = 30

and we said...

I'm unclear as to how you're defining the parent/child relationship. To find the children of id 30, there must be a column storing values that point to this parent row.

There are no rows which have 30 as a parent value. So I can't see which columns form the parent/child pair. If all you've got is the current row's value and its level, you're stuck!

Once you've defined the parent/child columns, the quick and easy way to do it is:

select * from ...
start with id = 30
connect by prior id = parent_column_id


I explain how to write hierarchical queries in more detail in this video:


Rating

  (3 ratings)

Comments

More details

Gots, November 30, 2020 - 4:37 pm UTC

The table data are stored as a simple treeview:
LineNumber : Simply the number of the line
Level : depth of the treeview
ID : Id of the Child

The table doesn't have the parent id.

I need to determine the child ID by using the depth the relation parent-child.
In my exemple :
My root id is 10
First child level id of 10 are 11, 20 and 30
Childs of 11 are 13, 14 and child of 14 is 15.

for example, I receive an ID for the where clause : 30
I want to get the build this result :

ParentID ChildID Path
30 31 30 31
31 33 30 31 33
30 32 30 32
30 34 30 34
34 35 30 34 35
35 36 30 34 35 36

Usually, we start from table with ParentID, ChildID and with "connect by" hierarchical query we obtain as reslult my starting table.

My need is like I want to reverse the process from result to data.
Chris Saxon
November 30, 2020 - 5:32 pm UTC

Sorry, but unless you have the columns storing the parent/child relationship there's no guaranteed way to reconstruct it.

For these specific data you can do something like this:

with rws as (
  select 1 LineNumber, 1 mylevel, 10 id from dual
  union all
  select 2 , 2 , 11 from dual
  union all
  select 3 , 3 , 13 from dual
  union all
  select 4 , 3 , 14 from dual
  union all
  select 5 , 4 , 15 from dual
  union all
  select 6 , 2 , 20 from dual
  union all
  select 7 , 2 , 30 from dual
  union all
  select 8 , 3 , 31 from dual
  union all
  select 9 , 4 , 33 from dual
  union all
  select 10 , 3 , 32 from dual
  union all
  select 11 , 3 , 34 from dual
  union all
  select 12 , 4 , 35 from dual
  union all
  select 13 , 5 , 36 from dual
)
  select prior id par_id, id, level, sys_connect_by_path( id, '/')
  from   rws
  start with id = 30
  connect by mylevel = prior mylevel + 1
  and prior id < id;
  
PAR_ID    ID    LEVEL    SYS_CONNECT_BY_PATH(ID,'/')   
   <null>    30        1 /30                            
       30    31        2 /30/31                         
       31    33        3 /30/31/33                      
       33    36        4 /30/31/33/36                   
       31    35        3 /30/31/35                      
       35    36        4 /30/31/35/36                   
       30    32        2 /30/32                         
       32    33        3 /30/32/33                      
       33    36        4 /30/32/33/36                   
       32    35        3 /30/32/35                      
       35    36        4 /30/32/35/36                   
       30    34        2 /30/34                         
       34    35        3 /30/34/35                      
       35    36        4 /30/34/35/36


But this relies on an implied ordering (the parent id is always lower than its children's). In the real world there are likely to be cases that break this rule. So it's almost certain to give wrong results at some point.

You need to go back to the source data!

"Reverse engineering" a connect by

Kim Berg Hansen, December 03, 2020 - 12:57 pm UTC

Given that the data is stated to be a "treeview" and in the followup review that it needs to be "reverse engineered", we can make some assumptions.

If we assume that the data we have are the output of a connect by query like this:

select
   rownum as linenumber
 , level as mylevel
 , id
from source_data
start with parent_id is null
connect by parent_id = prior id
order siblings by id;


Then we can assume that a parent is the last row before the current row that has a smaller level.

That enables us to find the parent id for example like this:

with rws as (
  select 1 LineNumber, 1 mylevel, 10 id from dual
  union all
  select 2 , 2 , 11 from dual
  union all
  select 3 , 3 , 13 from dual
  union all
  select 4 , 3 , 14 from dual
  union all
  select 5 , 4 , 15 from dual
  union all
  select 6 , 2 , 20 from dual
  union all
  select 7 , 2 , 30 from dual
  union all
  select 8 , 3 , 31 from dual
  union all
  select 9 , 4 , 33 from dual
  union all
  select 10 , 3 , 32 from dual
  union all
  select 11 , 3 , 34 from dual
  union all
  select 12 , 4 , 35 from dual
  union all
  select 13 , 5 , 36 from dual
)
select
   rws.id
 , (
      select max(id) keep (dense_rank last order by linenumber)
      from rws prev_parents
      where prev_parents.linenumber < rws.linenumber
      and prev_parents.mylevel < rws.mylevel
   ) as parent_id
from rws;


(I tried if I could do it with LAST_VALUE IGNORE NULLS, but couldn't. MATCH_RECOGNIZE isn't available on 11g. MODEL clause perhaps, but I couldn't think of way without spending a lot of time on it. The scalar subquery works, but if it's a large dataset, it'll probably not be fast.)

We can check if the reverse engineering was correct:

with rws as (
  select 1 LineNumber, 1 mylevel, 10 id from dual
  union all
  select 2 , 2 , 11 from dual
  union all
  select 3 , 3 , 13 from dual
  union all
  select 4 , 3 , 14 from dual
  union all
  select 5 , 4 , 15 from dual
  union all
  select 6 , 2 , 20 from dual
  union all
  select 7 , 2 , 30 from dual
  union all
  select 8 , 3 , 31 from dual
  union all
  select 9 , 4 , 33 from dual
  union all
  select 10 , 3 , 32 from dual
  union all
  select 11 , 3 , 34 from dual
  union all
  select 12 , 4 , 35 from dual
  union all
  select 13 , 5 , 36 from dual
),
source_data as (
   -- reverse engineered
   select
      rws.id
    , (
         select max(id) keep (dense_rank last order by linenumber)
         from rws prev_parents
         where prev_parents.linenumber < rws.linenumber
         and prev_parents.mylevel < rws.mylevel
      ) as parent_id
   from rws
)
select
   rownum as linenumber
 , level as mylevel
 , id
from source_data
start with parent_id is null
connect by parent_id = prior id
order siblings by id;


Looks to me like it gives the same output ;-)
Connor McDonald
December 04, 2020 - 3:16 am UTC

Nice stuff!

Sorry, missed some output in the previous review

Kim Berg Hansen, December 03, 2020 - 1:04 pm UTC

with rws as (
  select 1 LineNumber, 1 mylevel, 10 id from dual
  union all
  select 2 , 2 , 11 from dual
  union all
  select 3 , 3 , 13 from dual
  union all
  select 4 , 3 , 14 from dual
  union all
  select 5 , 4 , 15 from dual
  union all
  select 6 , 2 , 20 from dual
  union all
  select 7 , 2 , 30 from dual
  union all
  select 8 , 3 , 31 from dual
  union all
  select 9 , 4 , 33 from dual
  union all
  select 10 , 3 , 32 from dual
  union all
  select 11 , 3 , 34 from dual
  union all
  select 12 , 4 , 35 from dual
  union all
  select 13 , 5 , 36 from dual
)
select
   rws.id
 , (
      select max(id) keep (dense_rank last order by linenumber)
      from rws prev_parents
      where prev_parents.linenumber < rws.linenumber
      and prev_parents.mylevel < rws.mylevel
   ) as parent_id
from rws;

        ID  PARENT_ID
---------- ----------
        10
        11         10
        13         11
        14         11
        15         14
        20         10
        30         10
        31         30
        33         31
        32         30
        34         30
        35         34
        36         35

13 rows selected.

with rws as (
  select 1 LineNumber, 1 mylevel, 10 id from dual
  union all
  select 2 , 2 , 11 from dual
  union all
  select 3 , 3 , 13 from dual
  union all
  select 4 , 3 , 14 from dual
  union all
  select 5 , 4 , 15 from dual
  union all
  select 6 , 2 , 20 from dual
  union all
  select 7 , 2 , 30 from dual
  union all
  select 8 , 3 , 31 from dual
  union all
  select 9 , 4 , 33 from dual
  union all
  select 10 , 3 , 32 from dual
  union all
  select 11 , 3 , 34 from dual
  union all
  select 12 , 4 , 35 from dual
  union all
  select 13 , 5 , 36 from dual
),
source_data as (
   -- reverse engineered
   select
      rws.id
    , (
         select max(id) keep (dense_rank last order by linenumber)
         from rws prev_parents
         where prev_parents.linenumber < rws.linenumber
         and prev_parents.mylevel < rws.mylevel
      ) as parent_id
   from rws
)
select
   rownum as linenumber
 , level as mylevel
 , id
from source_data
start with parent_id is null
connect by parent_id = prior id
order siblings by id;

LINENUMBER    MYLEVEL         ID
---------- ---------- ----------
         1          1         10
         2          2         11
         3          3         13
         4          3         14
         5          4         15
         6          2         20
         7          2         30
         8          3         31
         9          4         33
        10          3         32
        11          3         34
        12          4         35
        13          5         36


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.