hierarchy queries of multiple tables

Question

i have one master table which have all details of customer,retailer,fse,dist and sub dist . i need to design hierarchy cust(1) -> ret(2) -> fse(3) - >dist(4) -> sub dist(5) tables master_table :

id    cust_mobile    type
1       9000230003   cust
2       8906784566   ret
3       7474747474   dist
4       4595274646   sdist
5       8588585958   fse
6       8588775958   cust
8       8588777758   dist

link_table

id  parent_id
1     2
2     8
3     7
4     5
6     3

i need output as

1,9000230003,cust,2,8906784566,ret,8,8588777758,dist
6 8588775958 cust,3,7474747474,dist

i want all levels associated with 1 customer id .

Answer 1

I really understand that solution from @Alex Poole is better and I voted up it. I only want add hard-coded query, that using SYS_CONNECT_BY_PATH

Code:

with t1 as (
    select 1 as id, 9000230003 as cust_mobile, 'cust' as type from dual
    union all
    select 2 as id, 8906784566 as cust_mobile, 'ret' as type from dual
    union all
    select 3 as id, 7474747474 as cust_mobile, 'dist' as type from dual
    union all
    select 4 as id, 4595274646 as cust_mobile, 'sdist' as type from dual
    union all
    select 5 as id, 8588585958 as cust_mobile, 'fse' as type from dual
    union all
    select 6 as id, 8588775958 as cust_mobile, 'cust' as type from dual
    union all
    select 8 as id, 8588777758 as cust_mobile, 'dist' as type from dual
    ),
    lt as (
    select 1 as id_, 2 as parent_id from dual
    union all
    select 2 as id_, 8 as parent_id from dual
    union all
    select 3 as id_, 7 as parent_id from dual
    union all
    select 4 as id_, 5 as parent_id from dual
    union all
    select 6 as id_, 3 as parent_id from dual
    )
    select replace(path,', ',',')
    from (
        select CONNECT_BY_ISLEAF as leaf, substr(SYS_CONNECT_BY_PATH(t2.id || ',' || t2.cust_mobile || ',' || t2.type, ', '),3) as path
        from  
        ( 
            select t1.*, lt.parent_id
            from t1 left join lt on t1.id = lt.id_ 
        ) t2 
        start with t2.type = 'cust' 
        connect by t2.id = prior t2.parent_id 
    ) t3
    where t3.leaf = 1

Result:

1,9000230003,cust,2,8906784566,ret,8,8588777758,dist
6,8588775958,cust,3,7474747474,dist

Answer 2

If you join your tables together first:

select mt.id, mt.cust_mobile, mt.type, lt.parent_id
from master_table mt
left join link_table lt on lt.id = mt.id;

        ID CUST_MOBIL TYPE   PARENT_ID
---------- ---------- ----- ----------
         1 9000230003 cust           2
         2 8906784566 ret            8
         3 7474747474 dist           7
         4 4595274646 sdist          5
         6 8588775958 cust           3
         5 8588585958 fse             
         8 8588777758 dist            

you can then use a hierarchical query against that, as an inline view or CTE, starting with any 'cust' entries:

with cte (id, cust_mobile, type, parent_id) as (
  select mt.id, mt.cust_mobile, mt.type, lt.parent_id
  from master_table mt
  left join link_table lt on lt.id = mt.id
)
select listagg(id ||','|| cust_mobile ||','|| type, ',')
  within group (order by level) as result
from cte
start with type = 'cust'
connect by id = prior parent_id
group by connect_by_root(id);

RESULT                                                                          
--------------------------------------------------------------------------------
1,9000230003,cust,2,8906784566,ret,8,8588777758,dist
6,8588775958,cust,3,7474747474,dist

This concatenates each row's relevant data into a single value separated by commas; and then each of those combined entries is put into a single result using listagg().

Just for fun, you could also use a recursive CTE (from 11gR2); here I've moved the initial concatenation inside the CTE just to separate it from the listagg():

with rcte (id, id_mobile_type, root_id, hop) as (
  select mt.id, mt.id ||','|| mt.cust_mobile ||','|| mt.type, mt.id, 1
  from master_table mt
  where mt.type = 'cust' -- starting condition
  union all
  select mt.id, mt.id ||','|| mt.cust_mobile ||','|| mt.type,
    rcte.root_id, rcte.hop + 1
  from rcte
  join link_table lt on lt.id = rcte.id
  join master_table mt on mt.id = lt.parent_id
)
select listagg(id_mobile_type, ',') within group (order by hop) as result
from rcte
group by root_id;

RESULT                                                                          
--------------------------------------------------------------------------------
1,9000230003,cust,2,8906784566,ret,8,8588777758,dist
6,8588775958,cust,3,7474747474,dist