-- Script to get all the categories from a given category up over
-- includes a mark against the path we are on and other categories at that level.
-- T.WAPPAT 20th July 2009
---
DECLARE @CatID Integer
SET @CatID=22141
WITH CategoryParents(CatID, ParentCatID, SortInLevel,
CatLangCode, CatDesc, InPath, Depth)
AS
(
--Anchor member
SELECT CategoriesSibs.CatID as CatID, CategoriesSibs.ParentCatID,
CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode,
CategoriesDesc.CatDesc,
CASE CategoriesSibs.CatID WHEN @CatID
THEN 1 ELSE 0 END as InPath,
0 as Depth
FROM MA_Categories AS Categories WITH (NOLOCK)
JOIN
MA_Categories as CategoriesChild WITH (NOLOCK)
ON CategoriesChild.ParentCatID=Categories.CatID
JOIN
MA_Categories as CategoriesSibs WITH (NOLOCK)
ON CategoriesSibs.ParentCatID=Categories.CatID
JOIN
MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK)
ON CategoriesDesc.CatID = CategoriesSibs.CatID
WHERE CategoriesChild.CatID=@CatID
UNION ALL
--Recursive member
select Categories.CatID, Categories.ParentCatID,
CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode,
CategoriesDesc.CatDesc,
CASE CategoriesSibs.CatID WHEN CategoryPArents.ParentCatID
THEN 1 ELSE 0 END as InPath,
Depth+1 as Depth
FROM CategoryParents AS CategoryParents
JOIN
MA_Categories AS Categories WITH (NOLOCK)
ON CategoryParents.ParentCatID=Categories.CatID
JOIN
MA_Categories as CategoriesSibs WITH (NOLOCK)
ON CategoriesSibs.ParentCatID=Categories.ParentCatID
JOIN
MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK)
ON CategoriesDesc.CatID = CategoriesSibs.CatID
)
-- execute CTE
select distinct * from CategoryParents ORDER BY Depth, SortinLevel