Tree structure in sql in Oracle.How to show tree,child nodes and parent nodes in SQL Oracle
Query - The whole tree structure:
SELECT *
FROM Employee
START WITH ParentID IS NULL
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
Query - The children of a given employee:
You do not need a hierarchical query for this.
(The parent is given by the bind variable :parent_id)
SELECT *
FROM Employee
WHERE ParentID = :parent_id
ORDER BY LastName, FirstName, ID;
Query - The descendants of a given employee:
The same query as for the whole tree but with a different start point
(The parent is given by the bind variable :parent_id)
SELECT *
FROM Employee
START WITH ParentID = :parent_id
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
Query - The employee and their ancestors:
Similar to the previous query but with the CONNECT BY reversed and you won't need to order the siblings as there will only be one immediate manager per employee.
(The employee is given by the bind variable :employee_id)
SELECT *
FROM Employee
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;
Query - The employee's manager:
Identical to the previous query but with a filter LEVEL = 2 to just get the immediate parent row.
(The employee is given by the bind variable :employee_id)
SELECT e.*
FROM Employee e
WHERE LEVEL = 2
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;
Oracle Tree Structure Hierarchy Data Display in Single Row Different Column
select h.Manager_entity_code
,h.Manager_entity_name
,h.Team_Lead_entity_code
,h.Team_Lead_entity_name
,h.Developer_entity_code
,h.Developer_entity_name
,c.client_name
from (select trim (both ',' from sys_connect_by_path (case when entity_role = 'Manager' then entity_code end,',')) as Manager_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Manager' then entity_name end,',')) as Manager_entity_name
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Team-Lead' then entity_code end,',')) as Team_Lead_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Team-Lead' then entity_name end,',')) as Team_Lead_entity_name
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Developer' then entity_code end,',')) as Developer_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Developer' then entity_name end,',')) as Developer_entity_name
from hierarchical_table
where connect_by_isleaf = 1
connect by parent_entity_code = prior entity_code
start with entity_code = 100
) h
join client_table c
on c.entity_code =
h.Developer_entity_code
order by h.Manager_entity_code
,h.Team_Lead_entity_code
,h.Developer_entity_code
;
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| MANAGER_ENTITY_CODE | MANAGER_ENTITY_NAME | TEAM_LEAD_ENTITY_CODE | TEAM_LEAD_ENTITY_NAME | DEVELOPER_ENTITY_CODE | DEVELOPER_ENTITY_NAME | CLIENT_NAME |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| 100 | Mack | 200 | Shail | 500 | Neha | Tata |
| 100 | Mack | 300 | Jack | 600 | Rocky | Rel |
| 100 | Mack | 300 | Jack | 600 | Rocky | Voda |
| 100 | Mack | 300 | Jack | 600 | Rocky | Airtel |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
For start with entity_code = 300
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| MANAGER_ENTITY_CODE | MANAGER_ENTITY_NAME | TEAM_LEAD_ENTITY_CODE | TEAM_LEAD_ENTITY_NAME | DEVELOPER_ENTITY_CODE | DEVELOPER_ENTITY_NAME | CLIENT_NAME |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| (null) | (null) | 300 | Jack | 600 | Rocky | Airtel |
| (null) | (null) | 300 | Jack | 600 | Rocky | Voda |
| (null) | (null) | 300 | Jack | 600 | Rocky | Rel |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
How to select data from child, it's parent and root of tree in oracle
As other suggest: CONNECT BY
WITH data (id, name, parent_id) AS (
SELECT 1, 'AAA', NULL FROM DUAL UNION ALL
SELECT 2, 'BBB', 1 FROM DUAL UNION ALL
SELECT 3, 'CCC', 2 FROM DUAL UNION ALL
SELECT 4, 'DDD', 3 FROM DUAL
)
SELECT
ID,
PARENT_ID,
ROOT_ID,
CHILD_NAME,
PARENT_NAME,
ROOT_NAME
FROM (
SELECT
ID,
NAME CHILD_NAME,
PARENT_ID,
CONNECT_BY_ROOT ID ROOT_ID,
PRIOR NAME PARENT_NAME,
CONNECT_BY_ROOT NAME ROOT_NAME
FROM
data
START WITH PARENT_ID IS NULL CONNECT BY PRIOR ID = PARENT_ID)
WHERE
PARENT_ID IS NOT NULL
ORDER BY
ID DESC
how to find the last level child nodes in tree kind structure in database using SQL
Use a hierarchical query and restrict the output to only the non-cyclic leaf rows using the pseudocolumns CONNECT_BY_ISLEAF and CONNECT_BY_ISCYCLE:
Oracle Setup:
CREATE TABLE your_table ( parent_column, child_column ) AS
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 2, 3 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 4, 5 FROM DUAL UNION ALL
SELECT 3, 6 FROM DUAL UNION ALL
SELECT 3, 7 FROM DUAL UNION ALL
SELECT 7, 8 FROM DUAL;
Query:
SELECT child_column
FROM your_table
WHERE CONNECT_BY_ISLEAF = 1
AND CONNECT_BY_ISCYCLE = 0
START WITH parent_column = 1
CONNECT BY NOCYCLE PRIOR child_column = parent_column
Output:
CHILD_COLUMN
------------
5
6
8
SQL Tree Structure
You didn't specify your DBMS but with standard SQL (supported by all modern DBMS) you can easily do a recursive query to get the full tree:
with recursive full_tree as (
select id, name, parent, 1 as level
from departments
where parent is null
union all
select c.id, c.name, c.parent, p.level + 1
from departments c
join full_tree p on c.parent = p.id
)
select *
from full_tree;
If you need a sub-tree, just change the starting condition in the common table expression. To get e.g. all "categories":
with recursive all_categories as (
select id, name, parent, 1 as level
from departments
where id = 2 --- << start with a different node
union all
select c.id, c.name, c.parent, p.level + 1
from departments c
join all_categories p on c.parent = p.id
)
select *
from all_categories;
Getting all leafs is straightforward: it's all nodes where their ID does not appear as a parent:
select *
from departments
where id not in (select parent
from departments
where parent is not null);
SQLFiddle example: http://sqlfiddle.com/#!15/414c9/1
Edit after DBMS has been specified.
Oracle does support recursive CTEs (although you need 11.2.x for that) you just need to leave out the keyword recursive. But you can also use the CONNECT BY operator:
select id, name, parent, level
from departments
start with parent is null
connect by prior id = parent;
select id, name, parent, level
from departments
start with id = 2
connect by prior id = parent;
SQLFiddle for Oracle: http://sqlfiddle.com/#!4/6774ee/3
See the manual for details: https://docs.oracle.com/database/121/SQLRF/queries003.htm#i2053935
Oracle SQL full hierarchy given any node as input
If you want to use it as view you can do something as the following:
WITH rek AS (SELECT item.id
, item.name
, connect_by_root item.id root_id
FROM item
START WITH parent_id IS null
CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
, startItem.name startName
, childItem.id childID
, childItem.name childName
FROM rek startItem
JOIN rek childItem
USING (root_id)
-- WHERE startItem.id = 3 -- This would be done from outside the view
The subquery rek connects all sibling of the tree with the root element. Then you only have to use this query twice and connect it via the root element to get all elements that are connected via parent-child relation.
If you want to reduce the resultset you can use the SYS_CONNECT_BY_PATH to do so:
WITH rek AS (SELECT item.id
, item.name
, connect_by_root item.id root_id
, SYS_CONNECT_BY_PATH(item.id, '/') path
FROM item
START WITH parent_id IS null
CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
, startItem.name startName
, childItem.id childID
, childItem.name childName
, childItem.path
FROM rek startItem
JOIN rek childItem
ON startItem.root_id = childItem.root_id
AND (startItem.path LIKE childItem.path||'/%'
OR childItem.path LIKE startItem.path||'/%'
OR childItem.id = startItem.id)
This for example will give you only the childs and parents of you starting point and no entries from other leafs.
Oracle sql query to get parent node based on type?
One issue has already been pointed out: connect by prior pid = cid. To stop when the type is DEP, you need to add one more condition to connect by:
connect by prior pid = cid and prior type != 'DEP'
and add a `WHERE clause:
where type = 'DEP'
(note that the WHERE clause comes before START WITH and CONNECT BY).
It is not clear what you want to SELECT, but that should be easy.
Actually, with the simple structure you have, the WHERE clause would suffice. Not having the additional condition in CONNECT BY means that the query will do just a little more work than necessary; it will find the MGT row as well, but it will still select the unique row where type = 'DEP'.
How to get all children of several nodes in tree structure?
;with C
as (
select AccountID,
ID,
ParentID,
0 as [level]
from tbl1
where ID IN (SELECT ID FROM Filteringtbl)
union all
select I.AccountID,
I.ID,
I.ParentID,
C.[level] + 1 as [level]
from tbl1 as I
inner join C on
C.ID = I.ParentID
)
select *
from C
How to make SQL query with grouping in each node of an hierarchy tree?
for example:
with
datum(id,parent_id,name,weight)
as
(
select 1,NULL,'N1',51 from dual union all
select 2 , 1 , 'N12' , 62 from dual union all
select 3 , 1 , 'N13' , 73 from dual union all
select 4 , 2 , 'N124' , 84 from dual union all
select 5 , 2 , 'N125' , 95 from dual
),
step1 as
(
select id,parent_id,name,weight, connect_by_root name root,connect_by_isleaf is_parent
from datum
connect by prior id = parent_id
)
select root,sum(weight) sum_w,
'('||listagg(weight,'+') within group(order by null) ||')' str_w,
'('||listagg(name,'+') within group(order by null) ||')' str_n
from step1
group by root
order by 1;
link: Hierarchical Queries
Related Topics
Oracle SQL Returns Rows in Arbitrary Fashion When No "Order By" Clause Is Used
Oracle Trigger Ora-04098: Trigger Is Invalid and Failed Re-Validation
Default Value of Guid in for a Column in MySQL
Reseed Identity Column in SQL Compact
Are There Downsides to Using Prepared Statements
How to Query Range of Data in Db2 with Highest Performance
Mssql: Update Statement Avoiding the Check Constraint
Splitting Variable Length Delimited String Across Multiple Rows (Sql)
How to Get the First and the Last Record Per Group in SQL Server 2008
How to Create Xml Schema from an Existing Database in SQL Server 2008
Java.Sql.Sqlexception: Ora-03115: Unsupported Network Datatype or Representation
Use Google Bigquery to Build Histogram Graph
Retrieving a Row, with Data from Key-Value Pair Table in MySQL
Prepend Table Name to Each Column in a Result Set in SQL? (Postgres Specifically)