SQL to retrieve tree structure nicely

Question

Given the simple data structure:

ID    |    Category_Name    |    Parent_ID

Example:

1          Cars                    0
2          Boxes                   0
3          Lamborghinis            1
4          VW Camper Vans          1
5          Big Boxes               2
6          Small Boxes             2
7          Cereal Boxes            2
8          Broken Lambos           3
9          Yellow Ones             3
10         Rusty                   8
11         Milkshake Stained       8
12         Chocolate Flavour       11
13         Strawberry              11
14         Indiscernible Solution  11

Representing a simple tree navigation structure, what would programatically be the best way to retrieve the tree in a presentable format? Can we create an SQL statement to retrieve them 'in order'?

Thanks for any help! If my approach is wrong, feel free to comment also.

I'm using SQL-Server 2000.

Answer 1

I usually build the tree structure in my application code. Partially because I'm more confident with c# than SQL, but it also because I usually need to process the data into suitable c# structures anyway.

SQL is quite bad at recursive structures like lists and trees. If I had to put the tree building in my database I'd go for a stored procedure. But there might be a smart way I don't know about.

If you use Oracle you might be able to hack something up with Connect By.

Answer 2

If you're using SQL Server 2008 you might want to try out the new hierarchyid data type.

If you're not then another way is to look into the nested sets model which works on all databases.

If you're using SQL Server 2005 and up you can use recursive CTEs to retreive the tree structure.

Answer 3

Not for SQL2000, but if you manage to upgrade to 2k5, you can do

WITH t AS(SELECT id, parent_id, category_name FROM mytable WHERE parent_id IS NULL
          UNION ALL
          SELECT c.id, c.parent_id, c.category_name FROM t p JOIN mytable c ON c.parent_id = p.id)
SELECT * FROM t