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.

What is the database platform? A number of the common SQL solutions are platform specific. — Ed Harper
– Ed Harper, Commented Aug 24, 2010 at 9:32
What Database are you using? If its MS-SQL 2005 and above, have you looked at Recursive CTE's ? — Jagmag
– Jagmag, Commented Aug 24, 2010 at 9:35

Mladen Prajdic · Accepted Answer · 2010-08-24 09:38:04Z

3

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.

answered Aug 24, 2010 at 9:38

Mladen Prajdic

15.7k2 gold badges46 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mladen Prajdic Over a year ago

then the only way is to use a while loop, insert into temp table and return those back. i'd urge you to look into the nested sets model, though.

Albin Sunnanbo · Accepted Answer · 2010-08-24 09:34:22Z

2

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.

answered Aug 24, 2010 at 9:34

Albin Sunnanbo

47.2k8 gold badges72 silver badges110 bronze badges

Comments

erikkallen · Accepted Answer · 2010-08-24 10:10:22Z

2

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

answered Aug 24, 2010 at 10:10

erikkallen

34.6k14 gold badges90 silver badges121 bronze badges

1 Comment

Tom Gullen Over a year ago

Thanks, very interesting! Is it possible for that to be broken down at all to work in SQL 2000 do you think?

Collectives™ on Stack Overflow

SQL to retrieve tree structure nicely

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related