SQL Recursive Query on Self Referencing Table (Oracle)

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_id
START WITH t1.id = 1
CONNECT BY PRIOR t1.id = t1.parent_id

Get data from self-referencing table in all directions


with
t as (
select id
from your_table
where name = 'D' -- your starting point
)
select id
from (
select id, parent_id from your_table
where parent_id is not null
union all
select parent_id, id from your_table
where parent_id is not null
union all
select id, null from t
)
start with parent_id is null
connect by nocycle prior id = parent_id

fiddle

Getting hierarchy data from self-referencing tables

If the database is SQL 2005 / 2008 then...

The easiest way to get this is using a CTE (Common Table Expression) that is designed to recurse.

 WITH myCTE (Item_id, Depth)
AS
(
Select Item_ID, 0 as Depth From yourTable where Item_Parent=0
Union ALL
Select yourTable.Item_ID, Depth + 1
From yourTable
inner join myCte on yourTable.item_Parent = myCte.Item_Id
)

Select Item_id, Depth from myCTE

The output is as follows:

Item_Id  Depth
1 0
2 0
3 1
4 1
5 2

From that you can format it as you wish.

Recursive query in Oracle

In Oracle this is easily done using CONNECT BY

select message_id, parent_id, message_content
from messages
start with message_id = 97 -- this is the root of your conversation
connect by prior message_id = parent_id;

This walks the tree from top to bottom.

If you want to walk the tree from a single message to the root, change the start with and the connect by part:

select message_id, parent_id, message_content
from messages
start with message_id = 100 -- this is the root of your conversation
connect by prior parent_id = message_id; -- this now goes "up" in the tree

recursive query to find the root parent in oracle

Using a recursive CTE you can do:

with table_a ( id1, child_id ) as (
select null, 1 from dual union all
select 1, 2 from dual union all
select 2, 3 from dual union all
select 3, NULL from dual union all
select 4, NULL from dual union all
select 5, 6 from dual union all
select 6, 7 from dual union all
select 7, 8 from dual union all
select 8, NULL from dual
),
n (s, e) as (
select id1 as s, child_id as e from table_a where id1 not in
(select child_id from table_a
where id1 is not null and child_id is not null)
union all
select n.s, a.child_id
from n
join table_a a on a.id1 = n.e
)
select
coalesce(e, s) as c, s
from n
order by s

Result:

 C S 
- -
3 1
1 1
2 1
4 4
5 5
7 5
8 5
6 5

As a side note, "Recursive CTEs" are more flexible than the old-school CONNECT BY.

How can I create a simple Select for a self referential table?

For reference, you could also do it without hierarchical extensions by using a self-join:

SELECT p1.id_area, p1.name, COALESCE(p2.name, '')
FROM perarea p1
LEFT JOIN perarea p2 ON (p1.id_areapadre = p2.id_area)


Related Topics



Leave a reply



Submit