Get children from parent child relation table

Id          Name                                               ParentId

----------- -------------------------------------------------- -----------

1           Arjun                                              NULL

2           Sarvesh                                            1

3           Nitin                                                 2

4           Gaurav                                             1

5           Mithesh                                            1

6           Ashish                                             2

7           Jay                                                  3

CREATE PROCEDURE GetAllChildren 

@ParentId INT

AS

BEGIN

WITH [CTE] AS (

    SELECT c.Id,c.Name,c.ParentId FROM ParentChildTable c WHERE c.[ParentId] = @ParentId

    UNION ALL

    SELECT c.Id,c.Name,c.ParentId  FROM [CTE] p, ParentChildTable c WHERE c.[ParentId] = p.[Id]

)

SELECT  * FROM [CTE]

END

exec GetAllChildren 1

OUTPUT:-

Id          Name                                               ParentId
----------- -------------------------------------------------- -----------
2           Sarvesh                                            1
4           Gaurav                                             1
5           Mithesh                                            1
3           Nitin                                                 2
6           Ashish                                             2
7           Jay                                                  3
8           Mayank                                            7









ChildName                                          ParentName
-------------------------------------------------- --------------------------------------------------
Sarvesh                                            Arjun
Gaurav                                             Arjun
Mithesh                                            Arjun
Nitin                                                 Sarvesh
Ashish                                             Sarvesh
Jay                                                   Nitin
Mayank                                             Jay

For getting result like above you should take self join to the table like below:-

ALTER PROCEDURE GetAllChildren 

@ParentId INT

AS

BEGIN

WITH [CTE] AS (

    SELECT c.Id,c.Name,c.ParentId FROM ParentChildTable c WHERE c.[ParentId] = @ParentId

    UNION ALL

    SELECT c.Id,c.Name,c.ParentId  FROM [CTE] p, ParentChildTable c 

    WHERE c.[ParentId] = p.[Id]

)

SELECT ([CTE].Name) AS [ChildName],(ParentChildTable.Name) AS [ParentName]  FROM [CTE] INNER JOIN ParentChildTable ON [CTE].ParentId=ParentChildTable.Id

END




Download complete script here