CTE for trees - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CTE for trees

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-05-28 : 17:48:45

Hi,

My database looks like:

CategoryID ParentID Title Sort

1 -1 Cars 1
2 1 Honda 1
3 -1 Bikes 2
4 1 Ford 2
5 1 Toyota 3
6 3 Kawasaki 1

How can I retrieve the values in the following order:
1, 2, 4, 5, 3, 6

I have:

WITH MYCTE(categoryID, parentID, Title, Sort)
(
SELECT TOP 1 categoryID, parentID, Title, Sort
FROM Categories
WHERE parentID = -1
ORDER BY Sort ASC

UNION ALL

SELECT c.categoryID, c.parentID, c.title, c.sort
FROM Categories c
INNER JOIN MYCTE cte ON (cte.categoryID = c.parentID)

)

SELECT *
FROM MYCTE

It doesn't seem to work though? Help! hehe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 17:50:56

Could you explain in words how to get that order?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-05-28 : 18:38:24

It is displaying all the top level categories in order, and each top level categories sub categories (if present).

1. Cars
- Honda
- Ford
- Toyota
2. Bikes
- Kawasaki

Make sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 03:02:17

[code]DECLARE @Sample TABLE (CategoryID INT, ParentID INT, Title VARCHAR(20), Sort INT)

INSERT @Sample
SELECT 1, -1, 'Cars', 1 UNION ALL
SELECT 2, 1, 'Honda', 1 UNION ALL
SELECT 3, -1, 'Bikes', 2 UNION ALL
SELECT 4, 1, 'Ford', 2 UNION ALL
SELECT 5, 1, 'Toyota', 3 UNION ALL
SELECT 6, 3, 'Kawasaki', 1

;WITH Yak (CategoryID, ParentID, Title, Sort, Path, Indent)
AS (
SELECT CategoryID,
ParentID,
Title,
Sort,
'\' + CAST(CategoryID AS VARCHAR(MAX)),
0
FROM @Sample
WHERE ParentID = -1

UNION ALL

SELECT TOP 100 PERCENT
s.CategoryID,
s.ParentID,
s.Title,
s.Sort,
y.Path + '\' + CAST(s.CategoryID AS VARCHAR(12)),
y.Indent + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.CategoryID = s.ParentID

ORDER BY Sort
)

SELECT CategoryID,
Title,
Indent
FROM Yak
ORDER BY Path[/code]

E 12°55'05.25"
N 56°04'39.16"

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-06-02 : 17:56:42

So I guess it would make it easier if I just maintain a 'Path' column in the database?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources