SQL: Recursive Path

Question

Is it possible to create a "tree resolver" in SQL?

I have a table:

ID Name Parent
1  a
2  b    1
3  c    1
4  d    3

Now I want a SQL query that returns:

ID   PATH
1    /a
2    /a/b
3    /a/c
4    /a/c/d

Is this possible with SQL? It would make many things easier for me. Any help would really be appreciated!

Answer 1

Yes it is, look here. You can use the "start with" and "connect by prior" statements, I've used this in the past to create breadcrumbs in a web app.

Answer 2

Depending on what database server use, this functionality may be provided for you already. Otherwise you can create a function that call itself to return this information, or implement a Materialized Path solution.

Update:

For DB2 you can make use of Recursive Common Table Expressions.

Answer 3

Make use of CTE if using Sql Server 2005. There is an excellent article here

Answer 4

There are several different ways to represent a tree in an SQL database. I guess I don't know much, but I do know that Django Treebeard uses 3 different ways to do it. If you look at the documentation, it has short descriptions of each way:

adjacency list -- what you're doing already

materialized path -- article: http://www.dba-oracle.com/t_sql_patterns_trees.htm

nested sets -- oh, here's wikipedia: http://en.wikipedia.org/wiki/Nested_set_model