Get all child nodes from table
<p>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</p>

<pre><code>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]
</code></pre>

<p>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.</p>

<p>Table</p>

<pre><code>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
</code></pre>

<p>Results</p>

<pre><code>pkID | parentID | CategoryName
6    | 0        | Category6
1    | 6        | Category1
9    | 7        | Category9 &lt;-- not a child of pkID=6
</code></pre>