/SQL/How to find all children of a record with only parent ID in sql

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

Source: stackoverflow.com

Similar codes

1 vote345 views1 answer
How to select rows with no matching entry in another table?
SQL
3 votes334 views5 answers
Mysql get last inserted id
SQL
0 vote354 views1 answer
Mysql enter ubuntu
SQL
0 vote365 views1 answer
How to make case insensitive in sql
SQL
0 vote335 views1 answer
How to write triggers to increment or decrement the number of employees
SQL
0 vote403 views1 answer
Laravel SQLSTATE[HY000] [2002] No such file or directory (SQL: select * from information_schema.tables where table_schema = testDB and table_name = migrations and table_type = 'BASE TABLE')
SQL
-1 vote339 views3 answers
Create temp table in sql
SQL
3 votes339 views2 answers
Sql random integer
SQL
0 vote389 views1 answer
Recently updated stored procedure in sql server
SQL
10 votes456 views4 answers
Sql server pagination
SQL