/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 vote133 views2 answers
Drop table with constraints
SQL
3 votes115 views2 answers
Sql asc
SQL
0 vote112 views1 answer
Sql server inner join convert collation
SQL
8 votes131 views1 answer
Oracle search stored procedures for text
SQL
7 votes143 views2 answers
Mysql Public Key Retrieval is not allowed
SQL
1 vote120 views1 answer
Host 127.0 0.1 is not allowed to connect to this mysql server
SQL
0 vote132 views2 answers
Unique key
SQL
0 vote119 views1 answer
Cast as decimal postgresql
SQL
11 votes177 views2 answers
Sql between date range
SQL
1 vote124 views1 answer
How to copy data of a table from another database to table of anaother database
SQL