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
Add Unique Constraint to Combination of Two Columns
Transfer Data from One Database to Another Database
How to Return Multiple Rows from the Stored Procedure? (Oracle Pl/Sql)
Select for Update with SQL Server
How to Use SQL Order by Statement to Sort Results Case Insensitive
How to Insert Data into Two Tables Simultaneously in SQL Server
SQL Server Select to JSON Function
The New Pivot Function in Bigquery
Concatenate Results from a SQL Query in Oracle
Compute Percents from Sum() in the Same Select SQL Query
Insert Command :: Error: Column "Value" Does Not Exist
How to Design a Database for User Defined Fields
How to Create a Copy of an Oracle Table Without Copying the Data
Select Data from Date Range Between Two Dates
Dynamic Sorting Within SQL Stored Procedures