Finding a Top Level Parent in SQL

Question

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         2    4         3 

This will form a tree like structure

          D           |           C           |           B           |           A 

ParentId and ChildId are foreign keys of Id column of Person Table

I need to write SQL that Can fetch me Top Level Parent i-e Root. Can anyone suggest any SQL that can help me accomplish this

Answer 1

You can use recursive CTE to achieve that:

DECLARE @childID INT  SET @childID  = 1 --chield to search  ;WITH RCTE AS (     SELECT *, 1 AS Lvl FROM RelationHierarchy      WHERE ChildID = @childID      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 

SQLFiddle DEMO

EDIT - for updated request for top level parents for all children:

;WITH RCTE AS (     SELECT  ParentId, ChildId, 1 AS Lvl FROM RelationHierarchy       UNION ALL      SELECT rh.ParentId, rc.ChildId, Lvl+1 AS Lvl      FROM dbo.RelationHierarchy rh     INNER JOIN RCTE rc ON rh.ChildId = rc.ParentId ) ,CTE_RN AS  (     SELECT *, ROW_NUMBER() OVER (PARTITION BY r.ChildID ORDER BY r.Lvl DESC) RN     FROM RCTE r  ) SELECT r.ChildId, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName FROM CTE_RN r INNER JOIN dbo.Person pp ON pp.id = r.ParentId INNER JOIN dbo.Person pc ON pc.id = r.ChildId WHERE RN =1 

SQLFiddle DEMO

EDIT2 - to get all persons change JOINS a bit at the end:

SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName FROM dbo.Person pc  LEFT JOIN CTE_RN r ON pc.id = r.CHildId AND  RN =1 LEFT JOIN dbo.Person pp ON pp.id = r.ParentId 

SQLFiddle DEMo

Answer 2

I've used this pattern to associate items in a hierarchy with the item's root node.

Essentially recursing the hierarchies maintaining the values of the root node as additional columns appended to each row. Hope this helps.

with allRows as (     select ItemId, ItemName, ItemId [RootId],ItemName [RootName]      from parentChildTable     where ParentItemId is null     union all     select a1.ItemId,a1.ItemName,a2.[RootId],a2.[RootName]     from parentChildTable a1     join allRows a2 on a2.ItemId = a1.ParentItemId )     select * from allRows