Oracle Database Recursive

Question

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

search for "connect by"

user330315
– user330315

2014-05-12 06:40:12 +00:00
Commented May 12, 2014 at 6:40 — user330315
– user330315, Commented May 12, 2014 at 6:40

Hazim Eid · Accepted Answer · 2015-06-16 05:29:03Z

2

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;

edited Jun 16, 2015 at 5:29

answered May 12, 2014 at 6:44

Hazim Eid

83513 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

The H2/SQL Server solution also works for Postgres, Firebird, DB2, SQLite, SQL Server and Oracle 11.2 . For Postgres and Firebird you need to add the keyword recursive to the CTE: with recursive link (....) - which is mandatory according to the SQL standard.

Collectives™ on Stack Overflow

Oracle Database Recursive

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related