Brekja Shmeauil Brekja Shmeauil -4 years ago 162
SQL Question

Oracle SQL: Find root parent for hierarchical data with child_id (No parent_id)

Say I have table A:

id child_id
1 2
2 3
3 NULL
4 NULL
5 6
6 7
7 8
8 NULL


I need a query to get root parent id of each row, this output for example:

id root_parent_id
1 1
2 1
3 1
4 4
5 5
6 5
7 5
8 5


I've tried both CONNECT BY and CTE by examples, but seems all I found based on rows with parent_id and not child_id and doesn't work.

CONNECT BY query I tried:

SELECT id, child_id, LEVEL, connect_by_root id
FROM a
CONNECT BY id = PRIOR child_id


CTE query I tried:

WITH recursion_view (base,
id,
child_id)
AS (
SELECT id base, id, child_id FROM a
UNION ALL
SELECT
previous_level.base,
current_level.id,
current_level.child_id
FROM recursion_view previous_level, a current_level
WHERE current_level.id = previous_level.child_id)
SELECT base,
id,
child_id
FROM recursion_view
ORDER BY base, id, child_id

Answer Source

You need to traverse the hierarchy in reverse order - view the root parents as leaves. Something like this:

with table_a ( id, child_id ) as (
       select 1, 2    from dual union all
       select 2, 3    from dual union all
       select 3, NULL from dual union all
       select 4, NULL from dual union all
       select 5, 6    from dual union all
       select 6, 7    from dual union all
       select 7, 8    from dual union all
       select 8, NULL from dual
     )
select     connect_by_root id as id, id as root_parent_id
from       table_a
where      connect_by_isleaf = 1
connect by child_id = prior id
order by   id
;

ID ROOT_PARENT_ID
-- --------------
 1              1
 2              1
 3              1
 4              4
 5              5
 6              5
 7              5
 8              5
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download