Finding a Top Level Parent in SQL
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
To find all top-level parents, use a query like:
select p.Name
from Person p
where not exists
(select null
from RelationHierarchy r
where r.ChildId = p.Id)
SQLFiddle here.
To find the top-level parent of a specific child, use:
with cte as
(select t.ParentId TopParent, t.ChildId
from RelationHierarchy t
left join RelationHierarchy p on p.ChildId = t.ParentId
where p.ChildId is null
union all
select t.TopParent TopParent, c.ChildId
from cte t
join RelationHierarchy c on t.ChildId = c.ParentId)
select p.name
from cte h
join Person p on h.TopParent = p.Id
where h.ChildId=3 /*or whichever child is required*/
SQLFiddle here.
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
Try this.
The recursive CTE will find the person and walk up the hierarchy until it finds no parent.
-- This CTE will find the ancestors along with a measure of how far up
-- the hierarchy each ancestor is from the selected person.
with ancestor as (
select ParentId as AncestorId, 0 as distance
from RelationHierarchy
where CHildId = ?
union all
select h.ParentId, a.distance + 1
from ancestor a inner join RelationHierarchy rh on a.AncestorId = rh.ChildId
)
select AncestorId
from ancestor
where distance = (select max(distance) from ancestor)