Friday, December 07, 2007

Expanding Tree SQL Query

I was working on my web site, and I wanted to implement expanding category view... I have done this many times, and I thought of sharing this with you all.

I have a category table like following..

catId int
catName nvarchar(255)
parcatId int

in which I will be entering categories, and this will allow me to have categories upto multiple levels.. for example I got parent category which has ID 1 and category name is Software Development, then I got categoies like Microsoft, Sun and Linux this three got catId 2,3,4 respectively and parcatId as 1.. then I got few more categories like .NET, MFC, Silverlight, WPF.. whcih got catid like 5,6,7,8 respectively and parcatId as 2.. so this allows me to have unlimited tree for categories.

now, what I wanted was, that if I say catId 8 which is WPF, then I wanted to have output which gives me all parent categories.. like Software Development > Microsoft > WPF... also I wanted if I give catId 1 which is Software development then to give me all categories belonging to that one.. so it gives me output.. Software Development > Microsoft, Sun, Linux > inside Microsoft > .NET, MFC, Silverlight, WPF.

to do the above thing.. following SQL Query I have written.

- to get explosion from top to bottom.

WITH ExpandedCat(catId, catName, parcatId) AS
(
SELECT Cat1.catId, Cat1.catName, Cat1.parcatId
FROM Categories AS Cat1
WHERE Cat1.catId=1

UNION ALL

SELECT Cat2.catId, Cat2.catName, Cat2.parcatId
FROM ExpandedCat, Categories Cat2
WHERE ExpandedCat.catId = Cat2.parcatId
)
SELECT DISTINCT catId, catName, parcatId
FROM ExpandedCat
ORDER BY parcatId


and to get explosion from bottom to top.

WITH ExpandedCat(catId, catName, parcatId) AS
(
SELECT Cat1.catId, Cat1.catName, Cat1.parcatId
FROM Categories AS Cat1
WHERE Cat1.catId=8

UNION ALL

SELECT Cat2.catId, Cat2.catName, Cat2.parcatId
FROM ExpandedCat, Categories Cat2
WHERE ExpandedCat.parcatId = Cat2.catId
)
SELECT DISTINCT catId, catName, parcatId
FROM ExpandedCat
ORDER BY parcatId

No comments: