Complex hierarchical query in SQL

Question

The following is the data setup for which SQL should be written.

Table:parchil par chil -------------------- E1 E2 E2 E3 E3 E4 E5 E6 E7 E8 Table:subval sub val -------------------- E1 10 E2 70 E3 30 E4 40 E5 60 E6 20 E7 50 Expected result: sub val -------------------- E1 150 E2 140 E3 70 E4 40 E5 80 E6 20 E7 50 

I have a request so far, which is long and far from elegant.

 select a.par,sum(b.val) from (select 'E1' as par,'E1' as chil from dual union all select 'E1' as par, chil from parchil start with par='E1' connect by prior chil=par union all select 'E2' as par,'E2' as chil from dual union all select 'E2' as par, chil from parchil start with par='E2' connect by prior chil=par union all select 'E3' as par,'E3' as chil from dual union all select 'E3' as par, chil from parchil start with par='E3' connect by prior chil=par union all select 'E4' as par,'E4' as chil from dual union all select 'E4' as par, chil from parchil start with par='E4' connect by prior chil=par union all select 'E5' as par,'E5' as chil from dual union all select 'E5' as par, chil from parchil start with par='E5' connect by prior chil=par union all select 'E6' as par,'E6' as chil from dual union all select 'E6' as par, chil from parchil start with par='E6' connect by prior chil=par union all select 'E7' as par,'E7' as chil from dual union all select 'E7' as par, chil from parchil start with par='E7' connect by prior chil=par ) a, subval b where a.chil=b.sub group by a.par order by a.par; 

Is there any way to solve this problem? Thanks.

Answer 1

You can use cte to do this;

 WITH cte(sub,val,par,chil, lev) AS ( SELECT s.sub, s.val, p.par, p.chil, 1 FROM subval s LEFT JOIN parchil p ON s.sub=p.par UNION ALL SELECT s.sub, s.val+c.val, p.par, p.chil, lev + 1 FROM subval s LEFT JOIN parchil p ON s.sub=p.par JOIN cte c ON c.sub=p.chil ) SELECT c1.sub,c1.val FROM cte c1 LEFT JOIN cte c2 ON c1.sub=c2.sub AND c1.lev < c2.lev WHERE c2.sub IS NULL ORDER BY sub; 

SQLfiddle for testing with .

... or you can use a regular hierarchical query;

 SELECT root, SUM(val) val FROM ( SELECT CONNECT_BY_ROOT sub root, val FROM subval s LEFT JOIN parchil p ON s.sub = p.par CONNECT BY sub = PRIOR chil ) GROUP BY root ORDER BY root 

Another SQLfiddle .

Answer 2

you can use connect_by_root

 select root, sum(val) from ( select chil, connect_by_root par root from parchil connect by par = prior chil start with par in (select par from parchil ) union all select par, par from parchil ) , subval where sub=chil group by root order by root ;