/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

0 vote172 views1 answer
Create boolean column in MySQL with false as default value?
SQL
0 vote185 views1 answer
Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'autos_id' in 'field list' in
SQL
0 vote196 views1 answer
Where in clause tsql
SQL
0 vote240 views1 answer
Generate random & unique mysql string
SQL
0 vote146 views1 answer
Mariadb list tables
SQL
1 vote176 views2 answers
Mysql filter by date mount
SQL
8 votes202 views1 answer
Alter tablespace add datafile autoextend
SQL
5 votes175 views1 answer
Minecraft java port number
SQL
0 vote229 views1 answer
Mysql subtract month from timestamp
SQL
3 votes185 views1 answer
Insert into table from another table mysql
SQL