Was doing some code yesterday where existed a CATEGORIES table with the fields: categoryID, parentID, category and was allowed any depth of sub-categories.
At one moment i needed to get the IDs of all sub-categories starting with a given node of the tree. And because i tend to let each tool do the job it is best designed for, i wanted SQL Server to return that info altogether. I knew i did something like that long time ago, but wanted to see if there is something new and better that can be used for that purpose. And of course there is: CTE (Common Table Expression) for MS SQL 2005 that it is mainly used for recursive query. Yeah.. i know... MS SQL 2005 so it's not like NEW, but it was surely new for me.
Using the CTE the job is reduced to the following query:

With cte_Categories AS

(

   Select categoryID, Category, ParentID

   From Categories

   Where categoryID = YOURID



   UNION ALL



   Select c.categoryID, c.Category, c.ParentID

   From Categories AS c

   Inner Join cte_Categories AS cv ON c.ParentID = cv.categoryID

)



Select *

From cte_Categories

Nice and easy, huh?