Get all descendants of a parent using CTE

 

Declare @TblLocations Table(Id Int, Name Varchar(50), ParentId Int)
Insert into @TblLocations(Id, Name, ParentId)
    Values(1, 'India', Null)
Insert into @TblLocations(Id, Name, ParentId)
    Values(2, 'Kerala', 1)    
Insert into @TblLocations(Id, Name, ParentId)
    Values(3, 'Tamil Nadu', 1)        
Insert into @TblLocations(Id, Name, ParentId)
    Values(4, 'Kochi', 2)        
Insert into @TblLocations(Id, Name, ParentId)
    Values(5, 'Chennai', 3)        
    
    
;With Locations As
(
    Select * From @TblLocations
    Where ParentId = 3
    Union All
    
    Select Child.* from @TblLocations Child Inner join Locations Parent on Child.ParentId = Parent.Id
)    
Select * from Locations