create treeview looping query

Question

i have designed a treeview structure as below: *parentID = 0 means tree root

ID    parentID
1        0 
2        0  
3        1
4        1
5        1
6        3
7        4
8        5
9        5
10       5
11       2
12       2
13      10

When i pass the parameter of "1" it will return the result of everything under Root "1", Expected result:

ID  parentID
1        0
3        1
4        1
5        1
6        3
7        4
8        5
9        5
10       5
13      10

possible to solve this with a single query?

Answer 1

In the way you stored the tree now, it is not possible to get the entire tree in one pure SQL query. You need to write a loop in some other language (C#, stored procedure that supports loops, ...) to fetch the tree.

This is a nice article describing how to work with your way of storing the tree (including fetching the tree and removing items from the tree).

Even more interesting, that article also describes a way to store trees in a database table that does allow fetching the entire tree in a single query. It's called pre-order tree traversal. You can look it up for more information. I've found a C# implementation. It involves some more logic than the way you are using now, but is more performant for all but the smallest trees.