Find the Parent given the child

Posted by Niladri.biswas under Sql Server category on | Points: 40 | Views : 1886
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

SET @ChildId = 14

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

Select Top 1
Parents ParentId
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


Parents	Name
14 Holly Huggins
10 Simran Motilal
6 Nishant Mandilwar

Hope this helps

Comments or Responses

Login to post response