Oracle hierarchical queries: How to find parents of a list of child, tree

oracle_hierarchical_queries.md

In this Gist will show you how to get parents of a list of child from an Oracle database.

Let's start with the table below:

Child	Parent
1	NULL
2	1
3	2
4	3
5	NULL
6	5

As we can see, childs 1 and 5 are roots because parent column is NULL. For childs 2 / 3 / 4 their parent respectively is 1. For child 6 it's parent is 5.

So, if you have a structure like above and you want to get the root parent, use the SQL query below:

SELECT 
    CONNECT_BY_ROOT child AS CHILD_ID,
    child AS PARENT_ID,
    level 
FROM 
    table1 
WHERE 
    parent IS NULL
CONNECT BY child = PRIOR parent
START WITH 
    child IN (2, 4, 5, 6)
;

Below the result:

CHILD_ID	PARENT_ID	LEVEL
2	1	2
4	1	4
5	5	1
6	5	2

level Is a special keyword on Oracle, that tell you the level of child to reach the root parent.

---

I would love to hear how much this Gist help you, thank you!

So i have the a table with 4 columns. ORIGINAL_DEVICE, MIGRATION_DEVICE, EFF_START_DATE, EFF_END_DATE.

IM TRYING TO WRITE A RECURSIVE SQL THAT WILL ANALYSE THE LEVELS OF MIGRATION FROM ORIGINAL DEVICE TO MIGRATION DEVICE AND TREE OT OUT SO I CAN DETERMINE IF ANY OF MY MIGRATIONS ARE MORE THAN 6 LAYERS. (EXAMPLE x-original device to y migration device (1 migration level). Now the migration device would become the original device and move demand to another device listed in migration device.(level 2) i want to be able to determine how many layers of migration each original_device migrates to.