CODEWITHSUNDEEP
sqloracle-databasehierarchical-data
Eman
Eman

Reputation: 21

Hierarchical query to show all children under each parent

Please see the attached images:

Visual Diagram of Tree

Visual diagram of hirarchy

Desired Query Output

Desired query output

As an example, this is the structure of the data as shown in image (diagram). I need a query that will show the same result in the second image (to show all the children under each parent).

Below is the code I tried (the results are not like what I'm looking for):

 SELECT parent,
        LEVEL,
        child

FROM  table1 

CONNECT BY NOCYCLE parent = PRIOR child;

Upvotes: 2

Views: 2317

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

I altered the column names in your table from "CHILD" and "PARENT" to "ID" and "PARENT_ID", respectively, to be a little less confusing.

Anyway, you need to use CONNECT_BY_ROOT(parent_id) to get what is being shown as "PARENT" in your desired output. If you do not care about the order of the rows in your result, this is simply:

SELECT connect_by_root(parent_id) "PARENT", id "CHILD"
FROM   table1
WHERE  connect_by_root(parent_id) is not null
CONNECT BY parent_id = prior id

If you do care about the order of the rows, it gets harder. You need to check the depth of each node in the tree and use that to order your results. That would be:

with hier as ( 
SELECT connect_by_root(parent_id) root_id, id, level, 
       case when connect_by_root(parent_id) is null then level else null end root_depth
FROM   table1
CONNECT BY parent_id = prior id
)
select h1.root_id "PARENT", h1.id "CHILD"
from hier h1 inner join hier h2 on h2.root_id is null and h2.id = h1.root_id
order by h2.root_depth, h1.root_id, h1.id

Here is a full example, with test data:

WITH table1 ( id, parent_id ) AS
( SELECT 'A', null FROM DUAL UNION ALL
  SELECT 'A1', 'A' FROM DUAL UNION ALL
  SELECT 'A2', 'A' FROM DUAL UNION ALL
  SELECT 'A3', 'A' FROM DUAL UNION ALL
  SELECT 'A11', 'A1' FROM DUAL UNION ALL
  SELECT 'A12', 'A1' FROM DUAL UNION ALL
  SELECT 'A21', 'A2' FROM DUAL UNION ALL
  SELECT 'A121', 'A12' FROM DUAL UNION ALL
  SELECT 'A122', 'A12' FROM DUAL ),
-- Solution begins here 
hier as ( 
SELECT connect_by_root(parent_id) root_id, id, level lvl, 
       case when connect_by_root(parent_id) is null then level else null end root_depth
FROM   table1
CONNECT BY parent_id = prior id
)
select h1.root_id "PARENT", h1.id "CHILD", h2.*
from hier h1 inner join hier h2 on h2.root_id is null and h2.id = h1.root_id
order by h2.root_depth, h1.root_id, h1.lvl, h1.id

+--------+-------+
| PARENT | CHILD |
+--------+-------+
| A      | A1    |
| A      | A2    |
| A      | A3    |
| A      | A11   |
| A      | A12   |
| A      | A21   |
| A      | A121  |
| A      | A122  |
| A1     | A11   |
| A1     | A12   |
| A1     | A121  |
| A1     | A122  |
| A2     | A21   |
| A12    | A121  |
| A12    | A122  |
+--------+-------+

Upvotes: 5

Related Questions