CODEWITHSUNDEEP
sqltree
Chris
Chris

Reputation: 9509

SQL: Recursive Path

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!

Upvotes: 5

Views: 10224

Answers (5)

p.mesotten
p.mesotten

Reputation: 1402

Suppose we have a simple table called DLFolder with the following columns:

| folderId | name | parentFolderId |

In Oracle you can use the sys_connect_by_path operation.

select fo.folderId as folder_id, sys_connect_by_path(fo.name, '/') as relname
from DLFolder fo
start with fo.parentFolderId=0
connect by prior fo.folderId = fo.parentFolderId

Will give the following result:

/1020_Training_Material
/1020_Training_Material/2000_IBBA
/1020_Training_Material/2000_IBBA/5000_FR
/1020_Training_Material/2000_IBBA/5050_NL

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions164.htm

Upvotes: 0

Troy
Troy

Reputation: 381

Using CTE in sql server 2005 and later, here's a snippet that I have to do this:

WITH Paths([Level], [FullPath], [ID]) AS 
(
    SELECT 
        0 AS [Level], 
        Name AS FullPath, 
        ID
    FROM dbo.Entity
    WHERE (ParentEntityID IS NULL)

    UNION ALL

    SELECT 
        p.[Level] + 1 AS [Level], 
        CASE RIGHT(p.[FullPath], 1) 
        WHEN '\' THEN p.[FullPath] + c.[Name] 
        ELSE p.[FullPath] + '\' + c.[Name] 
    END AS FullPath, 
    c.ID
    FROM dbo.Entity AS c 
    INNER JOIN Paths AS p ON p.ID = c.ParentEntityID
)
SELECT [FullPath], [ID]
FROM Paths

Upvotes: 7

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

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.

Upvotes: 1

rescdsk
rescdsk

Reputation: 8895

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

Upvotes: 1

Otávio Décio
Otávio Décio

Reputation: 74250

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.

Upvotes: 1

Related Questions