sql - How to get the nested child IDs from table using sql query

Question

I have two tables,Table1 and Table2. Table1 is having SectionIDs that need to be excluded from tree.

Table2 is having ParentID and its ChildIDs. There is a scenario A parentID has a child which is aparent of another child and so on. I need to get all the childIDs of parentID present in Table1 from Table2.

Example Table1 has SectionId 20000448 which is parent of 20000449 which is a parent of 20000543 (see the below image). I need to get all the childIds and subchildIds of 2000448.

i.e, result should be 2000448(which is present in Table1) have childIds 20000449,20000450,20000452,20000543,20000544,20000490,20000739,20000740,20000741. (Here I am taking SubchildIDs from 20000449. If 20000450 has childIDs they also need to be considered. )

I have tried to use CTE but it is not giving the expected result.

Any help will be appreciated.

标签： sqlsql-servercommon-table-expression

Answer 1

Assuming that you are using SQL Server, seems a pretty straightforward recursion. It's hard to test without a text example but try with the following:

;WITH Hierarchy AS 
(
    SELECT DISTINCT
        RootID = T.ParentID,
        DescendantID = T.ParentID,
        Level = 0
    FROM
        Table2 AS T
    WHERE
        NOT EXISTS (SELECT 'records without parents' FROM Table2 AS N WHERE N.ChildID = T.ParentID)

    UNION ALL

    SELECT
        RootID = H.RootID,
        DescendantID = T.ChildID,
        Level = H.Level + 1
    FROM
        Hierarchy AS H
        INNER JOIN Table2 AS T ON H.DescendantID = T.ParentID
)
SELECT
    H.RootID,
    H.DescendantID,
    H.Level
FROM
    Hierarchy AS H
ORDER BY
    H.RootID,
    H.Level

To exclude the records from your Table1 you can use NOT EXISTS at the end or at the recursion, depending on your needs (stopping at the recursion will invalidate posterior children of records from Table1).

EDIT: To work with sample data and temporary table:

IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
    DROP TABLE #Table2

CREATE TABLE #Table2 (
    ChildID INT,
    ParentID INT)

INSERT INTO #Table2 (
    ChildID,
    ParentID)
VALUES
    (20000449, 20000448),
    (20000450, 20000448),
    (20000452, 20000448),

    (20000543, 20000449),
    (20000544, 20000449),

    (20000490, 20000543),

    (20000739, 20000490),
    (20000740, 20000490),
    (20000741, 20000490)

;WITH Hierarchy AS 
(
    SELECT DISTINCT
        RootID = T.ParentID,
        DescendantID = T.ParentID,
        Level = 0
    FROM
       #Table2 AS T
    WHERE
        NOT EXISTS (SELECT 'records without parents' FROM #Table2 AS N WHERE N.ChildID = T.ParentID)

    UNION ALL

    SELECT
        RootID = H.RootID,
        DescendantID = T.ChildID,
        Level = H.Level + 1
    FROM
        Hierarchy AS H
        INNER JOIN #Table2 AS T ON H.DescendantID = T.ParentID
)
SELECT
    H.RootID,
    H.DescendantID,
    H.Level
FROM
    Hierarchy AS H
ORDER BY
    H.RootID,
    H.Level

Result:

RootID  DescendantID    Level
20000448    20000448    0
20000448    20000449    1
20000448    20000450    1
20000448    20000452    1
20000448    20000543    2
20000448    20000544    2
20000448    20000490    3
20000448    20000739    4
20000448    20000740    4
20000448    20000741    4