CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-query
Hazim Eid
Hazim Eid

Reputation: 835

Oracle Database Recursive

i have oracle database with table table_test:

id | p_id
---------
1  | null
2  | 1
3  | 2
4  | 1
5  | 3
6  | 4
7  | 3
8  | 5
9  | 6
10 | 7

and i need to get all hierarchy id(s) which id = x?. the result should be as following :

x? = 1 --> 1
x? = 2 --> 2,1
x? = 3 --> 3,2,1
x? = 4 --> 4,1
x? = 5 --> 5,3,2,1
x? = 6 --> 6,4,1
x? = 7 --> 7,3,2,1
x? = 8 --> 8,5,3,2,1
x? = 9 --> 9,6,4,1
x? = 10 --> 10,7,3,2,1

what is the SQL i should use to get these result?

Thanks So much all

Upvotes: 0

Views: 74

Answers (1)

Hazim Eid
Hazim Eid

Reputation: 835

Thanks all, I got the solution:

IN ORACLE SQL:

SELECT T.*
FROM table_test T
START WITH T.ID =X?
CONNECT BY PRIOR T.P_ID = T.ID

IN H2 & SQLServer SQL:

WITH LINK(ID, PARENT_ID) AS (
SELECT ID, PARENT_ID
FROM TABLE_TEST 
WHERE ID = X?
UNION ALL
SELECT TABLE_TEST.ID,  TABLE_TEST.PARENT_ID
FROM LINK 
INNER JOIN TABLE_TEST ON LINK.PARENT_ID = TABLE_TEST.ID)

SELECT *
FROM LINK 
ORDER BY ID;

Upvotes: 2

Related Questions