Query To get all child and sub childs of given root

Question

I Have table like below mentioned. table name parentChildTable

         ------------------------------------
        |    ID     | Description |Parent ID|
        -------------------------------------
        |  1101     |  Root        | ''     |
        |  1102     | Child of 1101| 1101   |
        |  1105     | Child of 1101| 1101   |
        |  1103     | Child of 1102| 1102   |
        |  1104     | Child of 1102| 1102   |
        |  1178     | Child of 1105| 1105   |
        |  11440    | Child of 1105| 1105   |
        |  11567    | Childof 11440| 11440  |
        |  12904Y   | Child of11567| 11567  |
        |  125687   | Child of 1101| 1101   |

now with 1101 --> root as parameter i need its child and sub childs till the no child level (I mean till leaves)

Sample output should be like this

        ------------
        |    ID     |
        -------------
        |  1102     |
        |  1105     | 
        |  1103     |
        |  1104     |
        |  1178     |
        |  11440    |
        |  11567    |
        |  12904Y   |
        |  125687   |

thanks in advance.

What have you already tried and what problem(s) did you experience? — 3N1GM4, Dec 09 '16 at 09:27
i tried by using union all on same table but the query i tried becoming large for each level with root as parameter @3N1GM4 — Aravind Goud, Dec 09 '16 at 09:31
It would be helpful to update your question with details of what you've already tried to show people that you have put some effort into this yourself already and aren't just looking for someone to do your work for you. FWIW, you could look into an iterative approach to drill down through the "levels" of child records until there are no more, building up a temp table of the results, then return that. — 3N1GM4, Dec 09 '16 at 09:33
Hey Arvind, try these http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/ — Bharat, Dec 09 '16 at 09:33
Actually i tried it with level of roots if there 2 levels i will be using union all for 2 times but the case i tried becoming complex as the level of child increases thanks @3N1GM4, now i will look into iterative approach — Aravind Goud, Dec 09 '16 at 09:42
@Bharat thanks iterative approach i didn't tried i will look into it — Aravind Goud, Dec 09 '16 at 09:42
A recursive CTE is definitely one way to do it, as an alternative to a purely iterative approach. I'll post an answer with an example of what I was imagining, for contrast. I've always found iteration easier to understand than recursive CTEs, although I don't doubt that performance is way better with a recursive CTE for larger datasets. — 3N1GM4, Dec 09 '16 at 09:45

score 1 · Answer 1 · answered Dec 09 '16 at 09:45

DECLARE @id VARCHAR(100)='1101'
;WITH tree(ID,Parent_ID)AS(
   SELECT '1101','' UNION
   SELECT '1102','1101' UNION
   SELECT '1105','1101' UNION
   SELECT '1103','1102' UNION
   SELECT '1104','1102' UNION
   SELECT '1178','1105' UNION
   SELECT '11440','1105' UNION
   SELECT '11567','11440' UNION
   SELECT '12904Y','11567' UNION
   SELECT '125687','1101' 
),cte AS(
   SELECT ID FROM tree WHERE ID=@id
   UNION ALL
   SELECT t.ID FROM tree AS t INNER JOIN  cte AS c ON  c.id=t.Parent_ID
)
SELECT * FROM cte WHERE cte.ID!=@ID
ORDER BY ID

score 1 · Accepted Answer · answered Dec 09 '16 at 09:47

-- Set up test data
DECLARE @parentChildTable TABLE (
    ID VARCHAR(50),
    [Description] VARCHAR(50),
    [Parent ID] VARCHAR(50)
)

INSERT INTO @parentChildTable
SELECT '1101','Root',''
UNION
SELECT '1102','Child of 1101','1101'
UNION
SELECT '1105','Child of 1101','1101'
UNION
SELECT '1103','Child of 1102','1102'
UNION
SELECT '1104','Child of 1102','1102'
UNION
SELECT '1178','Child of 1105','1105'
UNION
SELECT '11440','Child of 1105','1105'
UNION
SELECT '11567','Childof 11440','11440'
UNION
SELECT '12904Y','Child of11567','11567'
UNION
SELECT '125687','Child of 1101','1101'

-- Set initial root (could switch this for any valid ID value)
DECLARE @rootID VARCHAR(50)
SET @rootID = '1101'

-- Iterate to find results
CREATE TABLE #results (
    ID VARCHAR(50),
    Searched BIT
)

DECLARE @currentRoot VARCHAR(50)
SET @currentRoot = @rootID

WHILE (@currentRoot IS NOT NULL)
BEGIN
    INSERT INTO #results
    SELECT ID, 0 FROM @parentChildTable WHERE [Parent ID] = @currentRoot
    UPDATE #results SET Searched = 1 WHERE ID = @currentRoot
    SELECT @currentRoot = MIN(ID) FROM #results WHERE Searched = 0
END

SELECT ID FROM #results

DROP TABLE #results

Results:

I think this is specific not genric. suppose we have thousands of data in table then it means our query is going to be much larger. — Ashutosh Ojha, Dec 09 '16 at 10:22
@AshutoshOjha sorry, I'm not sure what you mean? This solution would work regardless of the number of records present - albeit performance might not be great on very large datasets. — 3N1GM4, Dec 09 '16 at 10:29
I mean query will increase as the number of records will increase. We need to union every single record in table — Ashutosh Ojha, Dec 09 '16 at 10:32
How do you mean? Yes, the query execution time will increase as the number of records increases, but the same query can be used. Are you confused by the test data I'm setting up in `@parentChildTable`, which is obviously just an example dataset based on the information given in the question? — 3N1GM4, Dec 09 '16 at 10:34

Query To get all child and sub childs of given root

2 Answers2