SQL to get the tree structure beautifully

Question

Given a 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 

Introducing a simple tree navigation structure, what would be the software best way to get a tree in a presentable format? Can we create an SQL statement to "order" them?

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

I am using SQL-Server 2000.

Answer 1

If you are using SQL Server 2008, you can try the new hierarchyid data type.

If you do not, then another way is to look into nested sets , which works in all databases.

If you are using SQL Server 2005 and you can use recursive CTEs to restore the tree structure.

Answer 2

I usually create a tree structure in my application code. Partly because I am more confident in C # than SQL, but also because I usually need to process the data in suitable C # structures.

SQL is pretty bad in recursive structures like lists and trees. If I had to put a tree in my database, I would go for a stored procedure. But there may be a sensible way that I don't know about.

If you use Oracle, you can hack something with Connect By .

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 tp JOIN mytable c ON c.parent_id = p.id) SELECT * FROM t