Alex Alex - 5 months ago 10
SQL Question

Get all child nodes from table

I am trying to get all child nodes from a table, but my query is picking up some extra results and I'm not sure why

SELECT [pkID]
,[parentID]
,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
UNION ALL
SELECT [Sub].[pkID]
,[Sub].[ParentID]
,[Sub].[CategoryName]
FROM [tblCategory] [Sub]
INNER JOIN [tblCategory] AS [Sub2] ON [Sub].[ParentID] = [Sub2].[pkID]


I am getting other child nodes when running this query
Note* In my VB.Net application (Where this SQL needs to be called) ParentID of 0 is a root node in the treeview.

Table

pkID | ParentID | CategoryName
1 | 6 | Category1
2 | 0 | Category2
3 | 0 | Category3
4 | 0 | Category4
5 | 0 | Category5
6 | 0 | Category6
7 | 0 | Category7
8 | 0 | Category8
9 | 7 | Category9


Results

pkID | parentID | CategoryName
6 | 0 | Category6
1 | 6 | Category1
9 | 7 | Category9 <-- not a child of pkID=6

Answer

It is because you need the same where statement on the bottom select for tblcategory WHERE [pkID] = 6. With out it you are getting the children of the entire table.

So something like this should work:

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
UNION ALL
SELECT [Sub].[pkID]
    ,[Sub].[ParentID]
    ,[Sub].[CategoryName]
FROM [tblCategory] [Sub]
INNER JOIN [tblCategory] AS [Sub2] ON [Sub].[ParentID] = [Sub2].[pkID]
WHERE [sub].[pkID] = 6

Simplified as Juan noted and I should have thought of can be done as a single where condition on the same select statement.

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
     or ParentId = 6

Here is a recursive cte method to get all of the children, grand children, great grand children, etc. of a parentid.

;WITH cte AS (
    SELECT [pkID]
       ,[parentID]
       ,[CategoryName]
       -- ,1 AS [Level]
    FROM [tblCategory]
    WHERE [pkID] = 6


    UNION ALL

    SELECT
       t.pkId
       ,t.[parentID]
       ,t.CategoryName
       -- ,[Level] + 1 AS [Level]
    FROM
       [tblCategory] t
       INNER JOIN cte c
       ON t.ParentId = c.pkId
)

SELECT *
FROM cte