Have you ever had to do a query on a table that joins to itself to form a hierarchy? For example the employee / manager relationships where the manager has a manager up to the top level of management.

My recent case involved categories of things where the categories could be nested to any level. I needed to find the oldest parent and the youngest parent for each leaf on the tree.

The lazy way (and often the quickest) is to write a set of statements with different levels of joins, then use your knowledge of the data to pull out what you need.

But what if you want to do in a way to impress the boss?

A recursive query will do the trick.

To get started, write a fairly simple query which we will call the Anchor query. This should get back the top level (root) of information. Then you JOIN this onto another query that does the recursion.

It is a very good idea to set a limit on the recursion so you don’t bring your SQL Server down. Actually – I think it defaults to 10,000 levels of recursion. But still, better to set a reasonable limit.

Here is an example:

 with CatHeirarchy (ProductID, CategoryID, ParentCategoryID, Name1, Name2, Level, TopParentName)
 as (
 -- Anchor definition
 select PCM.ProductID, PCM.CategoryID, C.ParentCategoryID,
 cast(C.Name as nvarchar(90)) AS Name1, Cast(C.Name as nvarchar(90)) as Name2, 0 as Level,
 CAST ('' as nvarchar(90)) as TopParentName
 from Product_Category_Map PCM
 inner join Category C on C.Id = PCM.CategoryId
 inner join Product P on P.Id = PCM.ProductId
 Union ALL
 -- recursive
 select A.ProductID, A.CategoryID, R.ParentCategoryID, cast(R.Name as nvarchar(90)) as Name1,
 CAST( A.Name2 as nvarchar(90)) as Name2, Level + 1,
 A.Name1
 from CatHeirarchy as A
 Inner Join Category as R
 on A.ParentCategoryID = R.ID
 )

select distinct CategoryID, Name2, TopParentName
 from CatHeirarchy ch
 , (Select MAX(Level) AS Level, ProductID
 from CatHeirarchy
 GROUP by ProductID) maxresults
 where ch.ProductID = maxresults.ProductID
 and ch.Level = maxresults.Level
 order by TopParentName, Name2
 OPTION (Maxrecursion 30)
 GO