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