Q&A for professional and enthusiast programmers
DECLARE @QuestionId INT = ##QuestionId:int?451415##, @IncludeDeletedPosts BIT = ##IncludeDeletedPosts:string?true##; /*To act as temporary index as base table doesn't have useful one*/ CREATE TABLE #PostLinks ( RelatedPostId INT, PostId INT, PRIMARY KEY (RelatedPostId, PostId) ); INSERT INTO #PostLinks SELECT RelatedPostId, PostId FROM PostLinks WHERE LinkTypeId = 3; WITH R AS (SELECT @QuestionId AS PostId, 0 AS Level, CAST(CONCAT('/', @QuestionId, '/') AS VARCHAR(8000)) AS Path UNION ALL SELECT pl.PostId, Level + 1, CONCAT(Path, pl.PostId, '/') FROM #PostLinks pl JOIN R ON R.PostId = pl.RelatedPostId WHERE Path NOT LIKE CONCAT('%/', pl.PostId, '/%')) SELECT PostId AS [Post Link], Level, Path FROM R WHERE Level > 0 AND ( @IncludeDeletedPosts = 1 OR PostId IN (SELECT Id FROM Posts) ) ORDER BY Path OPTION (RECOMPILE);