MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Table1
    (`PARENT_ID` int, `ID` int)
;
INSERT INTO Table1
    (`PARENT_ID`, `ID`)
VALUES
    (0, 1),
    (0, 2),
    (1, 3),
    (3, 4),
    (1, 5),
    (2, 6),
    (6, 7),
    (7, 8),
    (9, 10),
    (10, 11),
    (11, 9)
;

Records: 11  Duplicates: 0  Warnings: 0


with recursive cte(ancestor, child, lev, cycle) as (
  select id, id, 0, 0 from Table1
  union all
  select cte.ancestor
       , Table1.id
       , case when cte.ancestor = Table1.id then 0 else cte.lev + 1 end
       , case when cte.ancestor = Table1.id then cte.lev + 1 else 0 end
    from Table1
    join cte
      on cte.child = Table1.PARENT_ID
   where cte.cycle = 0
)
select distinct ancestor, child, lev
     , max(cte.cycle) over (partition by ancestor) as cycle
  from cte
  order by ancestor, lev, child


ancestor
child
lev
cycle


1
1
0
0

1
3
1
0

1
5
1
0

1
4
2
0

2
2
0
0

2
6
1
0

2
7
2
0

2
8
3
0

3
3
0
0

3
4
1
0

4
4
0
0

5
5
0
0

6
6
0
0

6
7
1
0

6
8
2
0

7
7
0
0

7
8
1
0

8
8
0
0

9
9
0
3

9
10
1
3

9
11
2
3

10
10
0
3

10
11
1
3

10
9
2
3

11
11
0
3

11
9
1
3

11
10
2
3

ancestor	child	lev	cycle
1	1	0	0
1	3	1	0
1	5	1	0
1	4	2	0
2	2	0	0
2	6	1	0
2	7	2	0
2	8	3	0
3	3	0	0
3	4	1	0
4	4	0	0
5	5	0	0
6	6	0	0
6	7	1	0
6	8	2	0
7	7	0	0
7	8	1	0
8	8	0	0
9	9	0	3
9	10	1	3
9	11	2	3
10	10	0	3
10	11	1	3
10	9	2	3
11	11	0	3
11	9	1	3
11	10	2	3

ancestor	child	lev	cycle
1	1	0	0
1	3	1	0
1	5	1	0
1	4	2	0
2	2	0	0
2	6	1	0
2	7	2	0
2	8	3	0
3	3	0	0
3	4	1	0
4	4	0	0
5	5	0	0
6	6	0	0
6	7	1	0
6	8	2	0
7	7	0	0
7	8	1	0
8	8	0	0
9	9	0	3
9	10	1	3
9	11	2	3
10	10	0	3
10	11	1	3
10	9	2	3
11	11	0	3
11	9	1	3
11	10	2	3

ancestor	child	lev	cycle
1	1	0	0
1	3	1	0
1	5	1	0
1	4	2	0
2	2	0	0
2	6	1	0
2	7	2	0
2	8	3	0
3	3	0	0
3	4	1	0
4	4	0	0
5	5	0	0
6	6	0	0
6	7	1	0
6	8	2	0
7	7	0	0
7	8	1	0
8	8	0	0
9	9	0	3
9	10	1	3
9	11	2	3
10	10	0	3
10	11	1	3
10	9	2	3
11	11	0	3
11	9	1	3
11	10	2	3