Get Recursive all childs by parentid in sql server
Table design for Relationship :
Query for get all childs by parentid in sql server :
Output of query :
Introduction : In this article i will show you how to get all recursive childs by parentid in sql server .To get all childs i have used recursive query using with CTE ( Common Table Expressions ) . It is very simple to with parentid and childId relationship table.I have written query for getting recursive child records in sql server is as follow .
Table design for Relationship :
Query for get all childs by parentid in sql server :
declare @ParentId as int;
set @ParentId = 1;
WITH RecursiveTable (ProductId, ParentId,ProductName, Level)
AS
(
SELECT MaintTab.ProductId,
MaintTab.ParentId,
MaintTab.ProductName ,
0 AS Level
FROM ProductTable AS MaintTab
WHERE ParentId = @ParentId
UNION ALL
SELECT MaintTab.ProductId,
MaintTab.ParentId,
MaintTab.ProductName ,
LEVEL + 1
FROM ProductTable AS MaintTab
INNER JOIN RecursiveTable Rtab ON
MaintTab.ParentId = Rtab.ProductId
)
SELECT * FROM RecursiveTable
Output of query :