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

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/12794237/sql-query-to-fetch-records-based-on-related-id-in-the-same-table

Stack Overflow

Q&A for programmers

DECLARE @Color TABLE(Id int, Name varchar(50), ParentId int);
DECLARE @targetParentId INT = 5 ;--For example
INSERT INTO @Color VALUES
(5, 'Color', 4),
(6, 'Blue', 5),
(7, 'Blue1', 6),
(8, 'Blue2', 6),
(9, 'Blue3', 6),
(10, 'Red', 5),
(11, 'Red1', 10),
(12, 'Red2', 10);

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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached