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!