SQL server tree limited depth query

Question

I have a tree I'm trying to store in a DB. Right now each row looks like

ID, Child1, Child2, Child3, NodeValue

I have Child1, Child2, and Child3 as foreign keys referencing ID.

What I want to do is: given a node, get that node and all its descendants (I guess a "subtree"). However, I want to limit the depth of the nodes to something around 4 to 7.

Does anyone have some pointers?

EDIT:

Here's an example:

ID   C1   C2   C3
1    10   52   32
2    NULL NULL NULL
3    4    5    6
4    2    NULL NULL
5    NULL NULL NULL
6    NULL NULL NULL
10   3    NULL NULL
52   NULL NULL NULL
32   NULL NULL NULL

And if I wanted a query of depth 2 on row 1 it would return the rows with ID's 1 , 10, 52, 32, and 3, but not 2, 4, 5, or 6

Answer 1

Use recursive CTE on SQLServer2005+

;WITH cte AS
 (
 SELECT ID, C1, C2, C3, 0 AS [Level]
 FROM dbo.test7
 WHERE ID = 1 -- @your root node
 UNION ALL
 SELECT t.ID, t.C1, t.C2, t.C3, c.[Level] + 1
 FROM dbo.test7 t JOIN cte c ON t.ID IN (c.C1, c.C2, c.C3)
 WHERE c.[Level] + 1 <= 2 --@your_depth
 )
 SELECT ID, C1, C2, C3
 FROM cte

Demo on SQLFiddle