SQL Query for a hierarchical structure

Question

I have been trying to write a query for a hierarchical structure.And I need to know if there is a better way to write this query apart from the below mentioned way. The structure is as per below for the pathology orders : Hospital -> Lab -> Section -> SubSection -> Bench/Instrument

There is separate table "resource" which saves all these columns as parent child relationships(child_resource,parent_resource) and also the type of resource. For example

Table Resource :

  parent_resource    child_resource    resource        resource_type

  DE Hospital        DE Section        DE Lab          Lab
  DE Lab             DF Sub Section    DF Section      Section
  DE Section         DE bench          DF SubSection   Bench
  DE Section         DF bench          DF SubSection   Bench
  DE Section         DG bench          DF SubSection   Bench

Another table Orders contains all the linkage of these 2 tables.

Table Orders :

  Order_id         resource_Type    Resource  

  12345            SubSection       DF SubSection
  23456            bench            DG bench
  34567            Section          DE Section

I want the results as

  Order_id   resource         Hospital       Lab      Section      Subsection      Bench      

  12345      DF SubSection    DE Hospital    DE Lab   DE Section   DF SubSection   -
  23456      DG bench         DE Hospital    DE Lab   DE Section   DF SubSection   DG bench
  34567      DE Section       DE Hospital    DE Lab   DE Section     -             -

For achieving the above results I can have multiple left joins depending on the resource_type, (If Resource type is subsection, it not be capturing Bench information),(If resource type is section, it should only be capturing hospital,lab,section and nothing else even though the information exists).

1st Left Join :

left outer join (select *

from resource rg

join resource rg_section on rg.child_resource = 
  rg_section.parent_resource
and rg_section.active_ind=1

join resource_group rg_subsection on rg_subsection.parent_resource = rg_section.child_resource
and rg_subsection.active_ind=1

where rg.active_ind=1
 ) sr_rs on 
 order.resource in (orders.resource_type(subsection))

2nd left join:

left outer join (select

from resource rg

join resource rg_section on rg.child_resource = rg_section.parent_resource
and rg_section.active_ind=1

join resource rg_subsection on rg_subsection.parent_resource = 
rg_section.child_resource
and rg_subsection.active_ind=1

join resource rg_bench on rg_bench.parent_resource = 
rg_subsection.child_resource
and rg_bench.active_ind=1

join resource sr on sr.service_resource_cd = rg_bench.child_resource
and sr.active_ind=1

where rg.active_ind=1
 ) sr_rs on 
  order.resource in (orders.resource_type(bench))

It looks like the query should be hierarchical (connect by) rather than repeated self joins. But, a few questions first... the values in column RESOURCE_TYPE in table ORDERS match the RESOURCE column of table RESOURCE - not the RESOURCE_TYPE column of that table. (By the way, by what insanity do you have a table with a column that has the same name as the table? Syntactically that is allowed, but did you think about the people who will need to maintain the code?) Then - why does table RESOURCE show both the parent and the child of a RESOURCE? That is a very unusual arrangement. — user5683823
– user5683823, Commented Jul 18, 2019 at 2:43
Also, in the last three rows of sample table RESOURCE, why is the RESOURCE_TYPE 'Bench' when the RESOURCE is 'DF SubSection'? That makes no sense. Shouldn't the type be 'SubSection'? — user5683823
– user5683823, Commented Jul 18, 2019 at 2:45
@mathguy Thanks for looking into it. My bad. I missed it while typing. With the name of the columns and table, I have tried to recreate the scenario from my production environment. I have changed the names of columns and tables to simplify the question !! The actually query that I have has around 15 tables and I wanted to do it as efficiently as possible. — Doodle
– Doodle, Commented Jul 18, 2019 at 3:21

Ponder Stibbons · Accepted Answer · 2019-07-18 16:42:19Z

1

Here is my try. I built hierarchy first then joined orders to it and conditionally grouped proper columns. The way the data is organized complicates task, but at the end I got expected results:

with hierarchy as (
    select r.*, level, connect_by_root(child_resource) root
      from resources r
      connect by prior resource_ = child_resource 
              or resource_ = prior parent_resource) 
select order_id, root, 
       max(case h.resource_type when 'Lab' then h.parent_resource end) hospital,
       max(case h.resource_type when 'Lab' then h.resource_ end) lab,
       max(case h.resource_type when 'Lab' then h.child_resource end) section,
       max(case h.resource_type when 'Section' then h.child_resource end) subsection,
       max(case h.resource_type when 'Bench' then h.child_resource end) bench
  from orders o join hierarchy h on h.root = o.resource_
  group by order_id, root order by order_id

^{dbfiddle demo}

Please check and test. I hope it will help.

answered Jul 18, 2019 at 16:42

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doodle Over a year ago

Thanks @ponder, I tried the similar thing yesterday and it worked out well. I just did it with a union all. I'll try with connect by as well.

Collectives™ on Stack Overflow

SQL Query for a hierarchical structure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related