SQL recursive query on self referencing table (Oracle)
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_idSTART WITH t1.id = 1 CONNECT BY PRIOR t1.id = t1.parent_id
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
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