0

Please login or register to vote for this query.

(click on this box to dismiss)

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);

fork query

edited jul 15 18

created jul 14 18 smithmartin