I have below table. Data arrange in parent child relationship.
create table tab1
(
id varchar2(10),
parent_id varchar2(10)
);
INSERT INTO tab1 VALUES ('A', NULL);
INSERT INTO tab1 VALUES ('B', 'A');
INSERT INTO tab1 VALUES ('C', 'B');
INSERT INTO tab1 VALUES ('D', 'B');
INSERT INTO tab1 VALUES ('E', 'D');
INSERT INTO tab1 VALUES ('F', 'D');
INSERT INTO tab1 VALUES ('G', 'A');
INSERT INTO tab1 VALUES ('H', 'G');
INSERT INTO tab1 VALUES ('I', 'A');
INSERT INTO tab1 VALUES ('J', 'I');
INSERT INTO tab1 VALUES ('K', 'J');
INSERT INTO tab1 VALUES ('X', 'K');
INSERT INTO tab1 VALUES ('Y', 'X');
INSERT INTO tab1 VALUES ('Z', 'Y');
INSERT INTO tab1 VALUES ('L', 'I');
There might be possibility of cyclic dependency i.e. A depend on B, B depend on C and C depend on A.
I want to find out member of each cyclic group
i.e. if member is part of cyclic group then it should be indicated with flag.
Also group detail should be display
i.e. J and K are member of (J,K) cyclic group
Similarlly X,Y and Z are member of (X,Y,Z) cyclic group and
A,I and J are member of (A,I,J) cyclic group
To create cyclic dependency below data is required.
Cyclic group between J and K
INSERT INTO tab1 VALUES ('J', 'K');
Cyclic group between X,Y and Z
INSERT INTO tab1 VALUES ('X', 'Z');
Cyclic group between A,I and J
INSERT INTO tab1 VALUES ('A', 'J');
Want final output as below
Here J is member of (A,I,J) and (J,K)
Could you help in finding above output.
Thanking in Advance
Ram