CODEWITHSUNDEEP
oracle-databasehierarchical-data
Rubio
Rubio

Reputation: 1095

Oracle hierarchical query - combine results

Given a table:

Old_ID  New_ID
A       B
B       C

Multiple ID changes can form a chain A -> B -> C. I can do a hierarchical query.

SELECT Old_ID, New_ID from mytable
CONNECT BY PRIOR New_ID = Old_ID

Since C is the valid new ID for both A and B, the result I'd like to get is:

Old_ID  New_ID
A       C
B       C

So map all old IDs to the latest new ID in the chain.

Upvotes: 0

Views: 73

Answers (1)

MT0
MT0

Reputation: 167822

Use a combination of CONNECT_BY_ROOT( old_id ), to get the start of the hierarchy, and WHERE CONNECT_BY_ISLEAF = 1, to find the last item in the hierarchy:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE your_table( Old_ID, New_ID ) As
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'B', 'C' FROM DUAL;

Query 1:

SELECT CONNECT_BY_ROOT( old_id ) AS old_id,
       new_id
FROM   your_table
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR New_ID = Old_id

Results:

| OLD_ID | NEW_ID |
|--------|--------|
|      A |      C |
|      B |      C |

Upvotes: 1

Related Questions