how to find all children of a record with only parent ID in sql 1

how to find all children of a record with only parent ID in sql

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 INT

SELECT @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

Here is what the above code is Doing:
1. Declare a table variable to hold the data.
2. Insert some data into the table variable.
3. Declare a variable to hold the ID of the record we want to find.
4. Use a recursive CTE to find all the records that have the ID of the record we want to find as a parent.
5. Select the results of the recursive CTE.

The results of the above code are:

ID ParentID NAME
2 1 B-1
4 2 C-1
5 2 C-2

Similar Posts