SQL query to retrieve records based on the corresponding identifier in one table

Question

Table structure:

I tried this:

select Id, Name from Color where ParentId=4 UNION select Id, Name from Color where ParentId=(select Id from Color where ParentId=4) 

The above static approach in this case should know all identifiers. I am looking for something dynamic, since I will only have the Color ParentId value.

As an example: for ParentId = 4 there are two entries; The name "Blue" and "Red" their identifier is 6 and 10 respectively. Now I have to get all the entries where Id 6 and 10.

Answer 1

Try the following:

 ;WITH cte AS ( SELECT Id, Name, ParentId FROM Color WHERE ParentId = @parentIdValue UNION ALL SELECT c2.Id, c2.Name, c2.ParentId FROM cte c1 INNER JOIN Color c2 ON c2.ParentId = c1.Id ) SELECT * FROM cte; 

Live demo