Find the Parent given the child

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

Comments or Responses

Login to post response