SQL Server parent child query example using recursive CTE (Common Table Expression)

Many times we have to write recursive parent child query (Multiple levels of Parent-Child Links)  in SQL Server. There are multiple approaches to achieve this

1. Using Hierarchy ID builtin CLR datatype in SQL Server 2008 or higher (not ion sql server 2000, 2005)
2. Using CTE (Common Table Expression) (sql server 2005 or higher)
3. Custom recursive function or stored proc.

I think Most effective way parent child query to get unknown levels of hierarchy is using…. Common Table Expression (CTE) feature of SQL Server (introduced in SQL Server 2005).

Common examples of multiple levels of nested parent child query are … getting parent branches of selected employee, navigate through org chart hierarchy using sql, navigate through product hierarchy using t-sql

Below is complete T-SQL example on how to write parent child query using CTE (2 examples… Find all Children of selected Parent node, Find all Parent Nodes or Selected Child Node)

Basic syntax of SQL CTE like below

WITH Recursive-CTE-Query AS (
  {Anchor Query}
  UNION ALL
  {Query joined to Recursive-CTE-Query}
)
SELECT * FROM Recursive-CTE-Query

Here is full example of SQL Server CTE to get hirarchy of parent child nodes stored in a table.

Example of recursive parent child query in SQL Server using CTE (Common Table Expression)

create table #ParentChildTable 
(nodeID int not null ,parentNodeID int null ,ClassType varchar(100) not null
)
go

insert into #ParentChildTable values(1,null,'Vehicle')
insert into #ParentChildTable values(2,1,'GroundVehicle')
insert into #ParentChildTable values(3,1,'AirVehicle')
insert into #ParentChildTable values(4,2,'Car')
insert into #ParentChildTable values(5,2,'Truck')
insert into #ParentChildTable values(6,2,'FlyingCar')
insert into #ParentChildTable values(7,4,'SUV')
insert into #ParentChildTable values(8,4,'VAN')
insert into #ParentChildTable values(9,8,'FordWindStar')
insert into #ParentChildTable values(10,3,'Airplane')
insert into #ParentChildTable values(11,4,'BMW-325')

 go

 --//Find all parent nodes of specified Child Node(s)
;WITH Heirarchy --//Create CTE which can be called recursively
AS
(
   SELECT Path=cast(T1.ClassType + '-' as varchar(1000)), Level=1, T1.NodeID,T1.ParentNodeID,T1.ClassType FROM #ParentChildTable T1
   WHERE T1.NodeID=9 -- or -- T1.NodeID IN (9,10) --//<< pass child node ID HERE
   UNION ALL
   SELECT Path1=cast(TH.Path + T1.ClassType + '-' as varchar(1000)),Level=TH.Level+1, T1.NodeID,T1.ParentNodeID,T1.ClassType
   FROM #ParentChildTable T1 INNER JOIN Heirarchy TH ON TH.parentNodeID = T1.NodeID --<< Child to Parent 
)
select * from Heirarchy

 --//Find all child nodes of Specified parent Node
;WITH Heirarchy --//Create CTE which can be called recursively
AS
(
   SELECT Path=cast(T1.ClassType + '-' as varchar(1000)), Level=1, T1.NodeID,T1.ParentNodeID,T1.ClassType FROM #ParentChildTable T1
   WHERE T1.NodeID=1 -- or -- T1.NodeID IN (9,10) --//<< pass parent node ID HERE
   UNION ALL
   SELECT Path=cast(TH.Path + T1.ClassType + '-' as varchar(1000)),Level=TH.Level+1, T1.NodeID,T1.ParentNodeID,T1.ClassType
   FROM #ParentChildTable T1 INNER JOIN Heirarchy TH ON TH.NodeID = T1.ParentNodeID --<< Parent to Child
)
select * from Heirarchy 

go
drop table #ParentChildTable 
go

Here is the output when you run these two queries

 Click here to learn more about CTE on MSDN