SQL recursive query on self referencing table (Oracle)
110,932
Solution 1
Use:
SELECT t1.id,
t1.parent_id,
t1.name,
t2.name AS parent_name,
t2.id AS parent_id
FROM tbl t1
LEFT JOIN tbl t2 ON t2.id = t1.parent_id
START WITH t1.id = 1
CONNECT BY PRIOR t1.id = t1.parent_id
Solution 2
What about using PRIOR,
so
SELECT id, parent_id, PRIOR name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id`
or if you want to get the root name
SELECT id, parent_id, CONNECT_BY_ROOT name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id
Solution 3
Using the new nested query syntax
with q(name, id, parent_id, parent_name) as (
select
t1.name, t1.id,
null as parent_id, null as parent_name
from t1
where t1.id = 1
union all
select
t1.name, t1.id,
q.id as parent_id, q.name as parent_name
from t1, q
where t1.parent_id = q.id
)
select * from q
Solution 4
Do you want to do this?
SELECT id, parent_id, name,
(select Name from tbl where id = t.parent_id) parent_name
FROM tbl t start with id = 1 CONNECT BY PRIOR id = parent_id
Edit Another option based on OMG's one (but I think that will perform equally):
select
t1.id,
t1.parent_id,
t1.name,
t2.name AS parent_name,
t2.id AS parent_id
from
(select id, parent_id, name
from tbl
start with id = 1
connect by prior id = parent_id) t1
left join
tbl t2 on t2.id = t1.parent_id
Comments
-
Maxim Veksler over 3 years
Lets assume I have this sample data:
| Name | ID | PARENT_ID | ----------------------------- | a1 | 1 | null | | b2 | 2 | null | | c3 | 3 | null | | a1.d4 | 4 | 1 | | a1.e5 | 5 | 1 | | a1.d4.f6 | 6 | 4 | | a1.d4.g7 | 7 | 4 | | a1.e5.h8 | 8 | 5 | | a2.i9 | 9 | 2 | | a2.i9.j10| 10 | 9 |
I would like to select all records start from accountId = 1, so the expected result would be:
| Name | ID | PARENT_NAME | PARENT_ID | ------------------------------------------- | a1 | 1 | null | null | | a1.d4 | 4 | a1 | 1 | | a1.e5 | 5 | a1 | 1 | | a1.d4.f6 | 6 | a1.d4 | 4 | | a1.d4.g7 | 7 | a1.d4 | 4 | | a1.e5.h8 | 8 | a1.e5 | 5 |
I am currently able to make the recursive select, but then I can't access the data from the parent reference, hence I can't return parent_name. The code I'm using is (adapted to the simplistic example):
SELECT id, parent_id, name FROM tbl START WITH id = 1 CONNECT BY PRIOR id = parent_id
What SQL should I be using to the mentioned above retrieval?
Additional key words for future seekers: SQL to select hierarchical data represented by parent keys in same table