Need to show grandchild under child under parent in Oracle Hierarchical query

Question

I have a table that has a tree of state, region, district, building and classes. Each row has a node id and a parent id. I am using the following

select name, child node, parent id from tableA  
connect by prior child node = parent id

i get:

CA  
  CENTRAL REGION  
    FRESNO DISTRICT  
     ST Jim BUILDING  
     ST joe BUILDING  
     st tom BUILDING  
     st sue BUILDING  
       JIMS CLASS  
       JOES CLASS  
       TOM CLASS  
       SUE CLASS

Problem is that at building level all buildings are listed one after another and then all the classes one after another. However I want to list the building and the class it is a parent of then the next building as follows

 FRESNO DISTRICT  
    st jim building  
      jims class  
    st joes building  
      joes class...

the node id and parent id are correct it just that all children are listed for a parent then all children for that child. it is not showing which child goes with the parent.

I want to show the tree from the top down such as CA, Central region, Fresno district, St Joe Building, Ms Mary class, Ms

score 3 · Accepted Answer · answered Apr 24 '11 at 08:33

The hierarchical ordering should be preserved, unless you use an ORDER BY without the SIBLINGS keyword.

select name, id, parent_id
from tableA
connect by prior id = parent_id
start with parent_id is null
order siblings by name;

Using this data:

create table tableA(id number, name varchar2(100), parent_id number);

insert into tableA
select 1 id, 'CA' name, null parent_id from dual union all
select 2, 'CENTRAL REGION', 1 from dual union all
select 3, 'FRESNO DISTRICT', 2 from dual union all
select 4, 'ST Jim BUILDING', 3 from dual union all
select 5, 'ST joe BUILDING', 3 from dual union all
select 6, 'st tom BUILDING', 3 from dual union all
select 7, 'st sue BUILDING', 3 from dual union all
select 8, 'JIMS CLASS', 4 from dual union all
select 9, 'JOES CLASS', 5 from dual union all
select 10, 'TOM CLASS', 6 from dual union all
select 11, 'SUE CLASS', 7 from dual;

Need to show grandchild under child under parent in Oracle Hierarchical query

1 Answers1