SQL Server: How to get all child records given a parent id in a self referencing table

You can try this

DECLARE @Table TABLE(        ID INT,        ParentID INT,        NAME VARCHAR(20))INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 1, NULL, 'A'INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 2, 1, 'B-1'INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 3, 1, 'B-2'INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 4, 2, 'C-1'INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 5, 2, 'C-2'DECLARE @ID INTSELECT @ID = 2;WITH ret AS(        SELECT  *        FROM    @Table        WHERE   ID = @ID        UNION ALL        SELECT  t.*        FROM    @Table t INNER JOIN                ret r ON t.ParentID = r.ID)SELECT  *FROM    ret

Unless you are using Oracle, your table structure is not suitable for the problem described. What you are attempting to do is grab a hierarchy (traversing a tree structure).

There is an article, More Trees & Hierarchies in SQL, that describes one method of solving the hierarchy problem. He basically adds a "lineage" column describing the hierarchy to every row.

Recursion in CTE looks bit expensive, so I have wrote this function which make use of recursive function call but much faster that CTE recursion.

CREATE FUNCTION [dbo].[Fn_GetSubCategories](@p_ParentCategoryId INT) RETURNS @ResultTable TABLE (       Id INT)ASBEGIN--Insert first level subcategories.INSERT INTO @ResultTable SELECT Id FROM Category WHERE ParentCategoryId = @p_ParentCategoryId OR Id = @p_ParentCategoryIdDECLARE @Id INTDECLARE @ParentCategory TABLE(Id INT)DECLARE cur_categories CURSORLOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT Id FROM Category WHERE ParentCategoryId = @p_ParentCategoryId and Id != @p_ParentCategoryIdOPEN cur_categoriesIF @@CURSOR_ROWS > 0     BEGIN      FETCH NEXT FROM cur_categories INTO @Id     WHILE @@FETCH_STATUS = 0     BEGIN        --Insert remaining level sub categories.        IF EXISTS(SELECT 1 FROM Category WHERE ParentCategoryId = @Id AND Id != @Id)        BEGIN            INSERT INTO @ResultTable            SELECT DISTINCT C.Id from Fn_GetSubCategories(@Id) C INNER JOIN @ResultTable R ON C.Id != R.Id        END     FETCH NEXT FROM cur_categories INTO @Id     END     --Delete duplicate records     ;WITH CTE AS     (SELECT *,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS RN FROM @ResultTable)     DELETE FROM CTE WHERE RN<>1ENDCLOSE cur_categoriesDEALLOCATE cur_categoriesRETURNEND