get a self join recursive for base id dynamically-CodePudding

I have a table that have a parent/child structure which the parent column point on the child column the child could be a parent or an ancestor...so I want the base Id for that parent this could be 3,4,5...etc. levels or even 1 level... this is the output that should be

    select 
    ID, 
    REF_ID, 
    ROUND_ID, 
    PARENT.ID BASE_ID --- HERE SHOULD BE THE ROOT ID OF THE PARENT
    FROM 
    TAB CHILD LEFT JOIN 
    TAB PARENT 
ON PARENT.ID = CHILD.REF_ID

CodePudding user response：

You can use a hierarchical query and find the leaves of the hierarchy:

SELECT CONNECT_BY_ROOT id AS id, 
       CONNECT_BY_ROOT ref_id AS ref_id, 
       CONNECT_BY_ROOT round_id AS round_id, 
       id AS base_id
FROM   TAB
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR ref_id = id;

Which, for the sample data:

CREATE TABLE tab (id, ref_id, round_id) AS
SELECT  1, NULL, 1 FROM DUAL UNION ALL
SELECT  2, NULL, 1 FROM DUAL UNION ALL
SELECT  3, NULL, 1 FROM DUAL UNION ALL
SELECT  4, NULL, 1 FROM DUAL UNION ALL
SELECT  5, NULL, 2 FROM DUAL UNION ALL
SELECT  6, NULL, 2 FROM DUAL UNION ALL
SELECT  7, 1,    2 FROM DUAL UNION ALL
SELECT  8, NULL, 2 FROM DUAL UNION ALL
SELECT  9, 2,    2 FROM DUAL UNION ALL
SELECT 10, 3,    2 FROM DUAL UNION ALL
SELECT 11, 4,    2 FROM DUAL UNION ALL
SELECT 12, NULL, 2 FROM DUAL UNION ALL
SELECT 13, NULL, 2 FROM DUAL UNION ALL
SELECT 14, NULL, 2 FROM DUAL UNION ALL
SELECT 15, NULL, 3 FROM DUAL UNION ALL
SELECT 16, 8,    3 FROM DUAL UNION ALL
SELECT 17, NULL, 3 FROM DUAL UNION ALL
SELECT 18, 9,    3 FROM DUAL UNION ALL
SELECT 19, NULL, 3 FROM DUAL UNION ALL
SELECT 20, NULL, 3 FROM DUAL UNION ALL
SELECT 21, NULL, 3 FROM DUAL;

Outputs:

ID REF_ID ROUND_ID BASE_ID

1 null 1 1

2 null 1 2

3 null 1 3

4 null 1 4

5 null 2 5

6 null 2 6

7 1 2 1

8 null 2 8

9 2 2 2

10 3 2 3

11 4 2 4

12 null 2 12

13 null 2 13

14 null 2 14

15 null 3 15

16 8 3 8

17 null 3 17

18 9 3 2

19 null 3 19

20 null 3 20

21 null 3 21

ID	REF_ID	ROUND_ID	BASE_ID
1	null	1	1
2	null	1	2
3	null	1	3
4	null	1	4
5	null	2	5
6	null	2	6
7	1	2	1
8	null	2	8
9	2	2	2
10	3	2	3
11	4	2	4
12	null	2	12
13	null	2	13
14	null	2	14
15	null	3	15
16	8	3	8
17	null	3	17
18	9	3	2
19	null	3	19
20	null	3	20
21	null	3	21

db<>fiddle here