CODEWITHSUNDEEP
sqlsql-server
Ashwini Verma
Ashwini Verma

Reputation: 7525

SQL query to fetch records based on related id in the same table

Table Structure:

enter image description here

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)

Above approach is static in this case I have to know all the Ids. I'm looking something dynamic approach since I would have only Color's ParentId value.

As example: For ParentId=4 there are two records; Name Blue and Red their Id is 6 and 10 respectively. Now here, I need to get all the records where Id 6 and 10.

Upvotes: 4

Views: 3235

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

;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

Upvotes: 6

Related Questions