Geek Answers Handbook

Find all parents based on a specific record

I need help with SQL code. I have two tables, the first is the table name

NameID Name 1 John 2 Paul 3 Jessica 4 Nancy 5 Sam 6 Jane 7 Jimmy

The second is the Family table

  FamilyID NameID ChildID 1 1 2 2 1 3 3 2 4 4 3 5 5 3 6 6 5 7

The "NameID" and "ChildID" fields in the Family table are connected to the "NameID" field in the Name table. Therefore, if I put it in a tree, it will be like

  John / \ Paul Jessica / / \ Nancy Sam Jane / Jimmy

I need SQL code that can find "all" parents for a specific record. For instance:

  • I want to know all the parents from Jane, the results will be: Jessica, John

  • I want to know all the parents from Jimmy, the results will be: Sam, Jessica, John

  • I want to know all the parents from Nancy, the results will be: Paul, John

+5
source share
2 answers

Here you go, use a recursive CTE as follows:

  DECLARE @pName VARCHAR(20) SET @pName = 'Jane' ;WITH RecursiveFamilyCTE AS ( SELECT ParentName.NAME, ParentName.NameID, f.ChildID FROM dbo.Family AS f JOIN NAME AS ChildName ON f.ChildID = ChildName.NameID JOIN Name AS ParentName ON f.NameID = ParentName.NameID WHERE ChildName.NAME = @pName UNION ALL SELECT ParentName.NAME, ParentName.NameID, f.ChildID FROM dbo.Family AS f JOIN NAME AS ChildName ON f.ChildID = ChildName.NameID JOIN Name AS ParentName ON f.NameID = ParentName.NameID JOIN RecursiveFamilyCTE ON f.ChildID = RecursiveFamilyCTE.NameID ) SELECT NAME FROM RecursiveFamilyCTE
+4
source

A recursive query is the way to go. Below is a flexible query that can be run without parameters.

 with familytree as ( select childid, nameid from family where nameid is not null union all select f.childid, t.nameid from familytree t inner join family f on t.childid = f.nameid ), treedetails as ( select p.nameid as parentid, p.name as parent, c.nameid as childid, c.name as child from familytree a left join name p on a.nameid = p.nameid left join name c on a.childid = c.nameid ) -- uncomment the query of your choice -- select * from treedetails where child = 'Jimmy' -- select * from treedetails where child = 'Jane' -- select * from treedetails where child = 'Nancy' -- -- show me children and grandchildren of Jessica -- select * from treedetails where parent = 'Jessica'

Example: http://www.sqlfiddle.com/#!3/f7030/4

0
source

More articles:


All Articles