Finding the Ultimate manager ID

Vignesh Murugan Published at Dev

Vignesh Murugan

The Problem Description:

I need to find the Ultimate Manager's Name (or ID) from a Given table for a Particular ID.
That is,For eg let:

3 has manager ID 4
4 has manager id 5,
5 has manager ID 109

So if I give cemp_id 3 I should get manager ID 109.

I have tried the following query:

SELECT C.CUSTOMERID       AS ChildId, 
       P.CUSTOMERID       AS ParentId, 
       P.PARENTCUSTOMERID AS GrantParentId, 
       C.CUSTOMERNAME 
FROM   CUSTOMER_PROFILE C 
       JOIN CUSTOMER_PROFILE P 
         ON C.PARENTCUSTOMERID = P.CUSTOMERID

but this only works on 2 levels of hierarchy and I need it for N levels of hierarchy.

Szymon

If you work with SQL Server 2008 or higher:

declare @id int = 1

;with cte
as
(
    select PARENTCUSTOMERID, 0 as level 
    from CUSTOMER_PROFILE where CUSTOMERID = @id

    union all

    select t.PARENTCUSTOMERID, cte.level  + 1 as level
    from CUSTOMER_PROFILE t
    inner join cte on t.CUSTOMERID = cte.PARENTCUSTOMERID 

)

select top 1 PARENTCUSTOMERID from cte
order by level desc

Collected from the Internet

Please contact [email protected] to delete if infringement.