Hierarchical Query Match With Multiple Tables Its Challenging

I have this business_table ref_ID name parent_id ----------------------------- ABC-0001 Amb NULL PQR-899 boss NULL tgv-632 pick NULL yyy-888 xyz

Solution 1:

This should return the expected result:

WITH hierarchy AS
 ( -- all rows from source tableSELECT b.ref_id, pc.parent_id, 
      0ASmatch,
      1AS lvl
   FROM business_table AS b
   LEFTJOIN parent_customer AS pc
     ON b.ref_id = pc.ref_id

   UNIONALLSELECT h.ref_id, pc.parent_id, 
      -- check if we found a match or reached top of hierarchyCASEWHEN mt.main_id ISNOTNULLOR pc.parent_id = pc.ref_id THEN1ELSE0END,
      lvl+1FROM hierarchy AS h
   JOIN parent_customer AS pc 
     ON pc.ref_id = h.parent_id -- going up in the hierarchyLEFTJOIN match_table_CM AS mt
     ON mt.main_id = pc.ref_id
   WHERE h.match =0-- no match yetAND lvl <10-- just in case there's an endless loop due to bad data
 )
SELECT*FROM hierarchy AS h
WHERE lvl = 
 ( -- return the last row, matching or notSELECTMax(lvl)
   FROM hierarchy AS h2
   WHERE h.ref_id = h2.ref_id
 );

Edit:

Rewrite using EXISTS because SQL Server doesn't support Outer Joins in the recursive part:

WITH hierarchy AS
 ( -- all rows from source tableSELECT b.ref_id, pc.parent_id, 
      0ASmatch,
      1AS lvl
   FROM business_table AS b
   LEFTJOIN parent_customer AS pc
     ON b.ref_id = pc.ref_id

   UNIONALLSELECT h.ref_id, pc.parent_id, 
      -- check if we found a match or reached top of hierarchyCASEWHENexists
            ( select*from match_table_CM AS mt
              where mt.main_id = pc.ref_id
            ) OR pc.parent_id = pc.ref_id
           THEN1ELSE0END,
      lvl+1FROM hierarchy AS h
   JOIN parent_customer AS pc 
     ON pc.ref_id = h.parent_id -- going up in the hierarchyWHERE h.match =0-- no match yetAND lvl <10-- just in case there's an endless loop due to bad data
 )
SELECT*FROM hierarchy AS h
WHERE lvl = 
 ( -- return the last row, matching or notSELECTMax(lvl)
   FROM hierarchy AS h2
   WHERE h.ref_id = h2.ref_id
 );

The optimizer's plan looked bad, so another rewrite to use a Windowed Aggregate instead of a Correlated Subquery:

WITH  hierarchy AS
 ( -- all rows from source tableSELECT b.ref_id, pc.parent_id, 
      0ASmatch,
      1AS lvl
   FROM business_table AS b
   LEFTJOIN parent_customer AS pc
     ON b.ref_id = pc.ref_id

   UNIONALLSELECT h.ref_id, pc.parent_id, 
      -- check if we found a match or reached top of hierarchyCASEWHENexists
            ( select*from match_table_CM AS mt
              where mt.main_id = pc.ref_id
            ) OR pc.parent_id = pc.ref_id
           THEN1ELSE0END,
      lvl+1FROM hierarchy AS h
   JOIN parent_customer AS pc 
     ON pc.ref_id = h.parent_id -- going up in the hierarchyWHERE h.match =0-- no match yetAND lvl <10-- just in case there's an endless loop due to bad data
 )
select*from 
 ( 
   SELECT h.*,
      max(lvl) over (partitionby ref_id) as maxlvl
   FROM hierarchy AS h
 ) as dt
WHERE lvl = maxlvl
;

Solution 2:

You can get the ultimate parent using a recursive CTE:

with cte as (select pc.ref_id, pc.parent_id as ultimate_parent, 1as lev
      from parent_customer pc
      where pc.ref_id = pc.parent_id
      union all
      select pc.ref_id, cte.ultimate_parent, lev + 1from cte
           parent_customer pc
           on pc.parent_id = cte.ref_id and pc.ref_id <> pc.parent_id
    )
select *
from cte;

You can put this in an update:

with cte as (select pc.ref_id, pc.parent_id as ultimate_parent, 1as lev
      from parent_customer pc
      where pc.ref_id = pc.parent_id
      union all
      select pc.ref_id, cte.ultimate_parent, lev + 1from cte
           parent_customer pc
           on pc.parent_id = cte.ref_id and pc.ref_id <> pc.parent_id
    )
update bt
    set parent_id = cte.ultimate_parent
    from business_table bt join
         cte
         on cte.ref_id = bt.ref_id