SQL recursive query

Question

I have a table category,

1) Id
2) CategoryName
3) CategoryMaster

with data like:

1 Computers 0
2 Software 1
3 Multimedia 1
4 Animation 3
5 Health 0
6 Healthsub 5

and I created a recursive query like:

;WITH CategoryTree AS ( SELECT *, CAST(NULL AS VARCHAR(50)) AS ParentName, 0 AS Generation FROM dbo.Category WHERE CategoryName = 'Computers' UNION ALL SELECT Cat.*,CategoryTree.CategoryName AS ParentName, Generation + 1 FROM dbo.Category AS Cat INNER JOIN CategoryTree ON Cat.CategoryMaster = CategoryTree.Id ) SELECT * FROM CategoryTree 

I get the results for the parent category below, for example, I get all the subcategories for the computer

but I want to get the results from the bottom up, for example, from animation to computers, please someone tell me the right direction.

Thanks in advance:)

Answer 1

Just swap the fields in the join clause:

 WITH CategoryTree AS ( SELECT *, 0 AS Generation FROM dbo.Category WHERE CategoryName = 'Animation' UNION ALL SELECT Cat.*, Generation + 1 FROM CategoryTree JOIN dbo.Category AS Cat ON Cat.Id = CategoryTree.CategoryMaster ) SELECT * FROM CategoryTree