T-SQL / SQL Server - How to recursively select all nested children IDs of a given parent?

Question

Consider the following table and hierarchy:

How can I select all children ids (10012, 10013, 10014) if I have id of parent 10011 (elegantly and efficiently)?

Here ParentId self references a parent folder.

Answer 1

Use a recursive cte

;WITH cte 
AS
(   SELECT
        b.FolderId, b.FolderName, b.ParentiD
    FROM
        table b
    WHERE
        FolderId= 10011
    UNION ALL
    SELECT
        e.FolderId, e.FolderName, e.ParentId
    FROM
        table  e
    INNER JOIN
        cte r ON e.ParentId = r.FodlerId
)
SELECT * FROM cte

Answer 2

Try this:

DECLARE @DataSource TABLE
(
    [FolderID] INT
   ,[FolderName] VARCHAR(12)
   ,[ParentID] INT
);

INSERT INTO @DataSource ([FolderID], [FolderName], [ParentID])
VALUES (10011, 'NEw Folder', NULL)
      ,(10012, 'NEw Folder', 10011)
      ,(10013, 'NEw Folder', 10012)
      ,(10014, 'NEw Folder', 10013)
      ,(10021, 'NEw Folder', NULL)
      ,(10022, 'NEw Folder', 10021)
      ,(10023, 'NEw Folder', 10022);

DECLARE @ParentID INT = 10011;

WITH DataSource AS
(
    SELECT *
    FROM @DataSource
    WHERE [FolderID] = @ParentID
    UNION ALL
    SELECT A.*
    FROM DataSource R
    INNER JOIN @DataSource A
        ON R.[FolderID] = A.[ParentID]
)     
SELECT DISTINCT [FolderID]
FROM DataSource
WHERE [FolderID] <> @ParentID;

Answer 3

simple self-join will help you

select e.folderid from table1 e inner join table1 t on e.parentid = t.folderid where t.parentid = 10011

try the above query :)