Find the Parent given the child

We often come across situations where given a child, we need to find the Parent

Let us consider the below

Parentid 	Childid	Name
NULL		1	Niladri Biswas
1		2	Prantik Das
1		3	Karan Basu
1		4	Deepak Kumar Goyal
2		5	Sachin Srivastav
NULL		6	Nishant Mandilwar
5		7	Arinmdam Pal
5		8	Mahi Sharma
3		9	Mahima Roy
6		10	Simran Motilal
9		11	Raj Malhotra
9		12	Sharmistha Roy
10		13	Preeti Sen
10		14	Holly Huggins

Here we have ParentId, ChildId and the Names. Now our aim is to find out the top level parent given the ChildId. For example, given Child Id = 14, the desired output will be 6.

Script for creation of data

DECLARE @ParentChild TABLE(Parentid INT,Childid INT,Name Varchar(20) );
INSERT INTO @ParentChild 
	SELECT null, 1, 'Niladri Biswas' UNION ALL 	
	SELECT 1, 2 ,'Prantik Das' UNION ALL  
	SELECT 1,3 ,'Karan Basu' UNION ALL  
	SELECT 1,4,'Deepak Kumar Goyal'  UNION ALL
	SELECT 2,5, 'Sachin Srivastav' UNION ALL 
	SELECT null,6, 'Nishant Mandilwar' UNION ALL 
	SELECT 5,7 ,'Arinmdam Pal' UNION ALL 
	SELECT 5,8,'Mahi Sharma' UNION ALL 
	SELECT 3,9, 'Mahima Roy' UNION ALL 
	SELECT 6,10, 'Simran Motilal' UNION ALL 
	SELECT 9,11,'Raj Malhotra' UNION ALL
	SELECT 9,12,'Sharmistha Roy' UNION ALL 
	SELECT 10,13,'Preeti Sen'  UNION ALL 
	SELECT 10,14,'Holly Huggins' 

The below query will help us to do so

DECLARE @ChildId INT
SET @ChildId = 14

;WITH GetParentsOfChild_Cte AS
(
    SELECT 
		Rn = ROW_NUMBER() Over(Order By (Select 1))
		,Childid AS Parents
		,Parentid	
		,Name	
    FROM @ParentChild
    WHERE Childid = @ChildId
    UNION ALL
    SELECT 
		Rn + 1
		,pc.Childid as Parents
		,pc.Parentid
		,pc.Name
    FROM @ParentChild pc
    JOIN GetParentsOfChild_Cte gp on pc.Childid = gp.Parentid
)

Select Top 1 
	Parents ParentId
	,Name
from GetParentsOfChild_Cte
Order By Rn Desc

The output is

ParentId	Name
6		Nishant Mandilwar

If we want to see all the intermediate level parents, run the below query

Select Parents,Name
from GetParentsOfChild_Cte

Output

Parents	Name
14	Holly Huggins
10	Simran Motilal
6	Nishant Mandilwar

Hope this helps