SQL Server query to get nested child records based on id provided by user-CodePudding

I have SQL Server data in the below format:

In the above table, parentid and sourceid are related, like the parent-child relationship.

in first-row parentid 'A' is sourceid of the second row. User will provide input of sourceid and based on that sourceid, I need to get its related child records.

For example, if the user provides input source id as 'A1', the output should be as shown below:

I tried using self join, but I am not able to get related child records in the table.

select * 
from testrecords1 t1
join testrecords1 t2 on t1.parentid = t2.sourceid
where t1.sourceid = 'A1'

This query results in only one record. Please provide corrections / suggestions to achieve the desired output.

CodePudding user response：

You can use Common Table Expression (CTE) for the recursive query.

The query could be written as:

;with MyCTE 
as
   (  
      SELECT Parentid, Sourceid from testrecords1 where SourceId = @SourceId
        UNION ALL  
      SELECT t1.Parentid, t1.Sourceid from testrecords1 t1
      inner join MyCTE t2 on t1.SourceId = t2.Parentid
    )  
 
SELECT * FROM MyCTE

Where @SourceId is the parameter for filter.