/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 vote127 views1 answer
Select a row include list of array with join table SQL
SQL
0 vote144 views1 answer
Set root password mysql xampp
SQL
0 vote119 views1 answer
Error in connection_import_file(conn@ptr, name, value, sep, eol, skip) : RS_sqlite_import: test.csv line 2 expected 11 columns of data but found 1
SQL
4 votes130 views3 answers
How to describe a table in sqlite3
SQL
0 vote129 views1 answer
On update current_timestamp jpa
SQL
12 votes157 views1 answer
Create column sql server
SQL
0 vote132 views1 answer
SQL Server dynamic pivot unknown number of columns
SQL
3 votes129 views2 answers
SqlMessage: "Host '192.168.64.1' is not allowed to connect to this MariaDB server
SQL
8 votes129 views6 answers
Sql insert
SQL
0 vote141 views1 answer
Make parameters nullable in sql server
SQL