Hierarchical Data In Oracle SQL

Question

I have tbl_parent like this in Oracle SQL, sample data is given below:

 Id(primary key)   parentid   childid

  1                 1           2
  2                 1           3
  3                 2           1
  4                 3           1  -- This row is wrong

In above table, some rows are incorrectly inserted, for example, if parent_id 1 has child_id 3 then parent_id 3 should not have child_id 1 as 3 is already child of 1 so can not be parent, I have 5000+ rows and want to find these incorrect rows, any help please?

Answer 1

Basically you are looking for cycles in your table.

The Oracle functionality to indentify cycles in hierarchical query is

CONNECT BY NOCYCLE and CONNECT_BY_ISCYCLE

This query show all nodes that lead to cycle - column is_Cycle = 1

select tbl.* ,
CONNECT_BY_ISCYCLE  is_Cycle, 
SYS_CONNECT_BY_PATH(childid, '/') path
from tbl
CONNECT BY NOCYCLE PRIOR childid = parentid

For your data the result is

  PARENTID    CHILDID   IS_CYCLE PATH     
---------- ---------- ---------- ----------
         1          2          0 /2         
         2          1          1 /2/1       
         1          3          1 /2/1/3     
         1          3          0 /3         
         3          1          1 /3/1       
         1          2          1 /3/1/2     
         2          1          0 /1         
         1          2          1 /1/2       
         1          3          1 /1/3       
         3          1          0 /1         
         1          2          1 /1/2       
         1          3          1 /1/3 

Note taht each cycle is recognised on several places, so you get some redundant data.

The advantage of this apprach is, that it works for longer cycles too (where the simple GROUP BY approach fails).

Example for cycle of the length 3:

create table tbl as
select 1 parentid, 2   childid from dual union all
select 2 parentid, 3   childid from dual union all
select 3 parentid, 1   childid from dual;

  PARENTID    CHILDID   IS_CYCLE PATH     
---------- ---------- ---------- ----------
         1          2          0 /2         
         2          3          0 /2/3       
         3          1          1 /2/3/1     
         2          3          0 /3         
         3          1          0 /3/1       
         1          2          1 /3/1/2     
         3          1          0 /1         
         1          2          0 /1/2       
         2          3          1 /1/2/3 

Answer 2

greatest and least functions might be used as

select least(parentid,childid) as least_par_chi_id,
       greatest(parentid,childid) as greatest_par_chi_id
  from tab
 group by greatest(parentid,childid), least(parentid,childid)
having count(*)>1;