Find Top Level Parent And With Recursive Cte

I have got two tables as following Table Person Id Name 1 A 2 B 3 C 4 D 5 E Table RelationHierarchy ParentId CHildId 2 1 3

Solution 1:

I think this solves your problem. The idea is to force the start on the child rather than doing a lookup initially:

WITH RCTE AS
  (
    SELECT @childID as parentId, NULL as childid, 1 AS Lvl

    UNION ALL

    SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
    )
   SELECT TOP 1 id, Name
   FROM RCTE r
   inner JOIN dbo.Person p ON p.id = r.ParentId
   ORDER BY lvl DESC;