Recursive calculation to form a tree using sql

Question

I am working on a simple problem and wanted to solve it using SQL. I am having 3 tables Category, Item & a relational table CategoryItem. I need to return count of items per category but the twist is Categories are arranged in Parent-Child relationships and the count of items in child categories should be added to the count in its parent Category. Please consider the sample data below and the expected resultset using SQL.

Id  Name    ParentCategoryId
1   Category1   Null
2   Category1.1 1
3   Category2.1 2
4   Category1.2 1
5   Category3.1 3

ID  CateoryId   ItemId
1   5              1
2   4              2
3   5              2
4   3              1
5   2              3
6   1              1
7   3              2

Result:

CategoryNAme     Count
Category1          7
Category1.1        5
Category2.1        4
Category1.2        1
Category3.1        2

I can do it in my business layer but performance its not optimal because of size of data. I am hoping if I can do it in data layer, I would be able to improve performance greatly.

Thanks in Advance for your reply

Answer 1

your tables and sample data

create table #Category(Id int identity(1,1),Name Varchar(255),parentId int)
INSERT INTO #Category(Name,parentId) values
('Category1',null),('Category1.1',1),('Category2.1',2),
('Category1.2',1),('Category3.1',3)

create table #CategoryItem(Id int identity(1,1),categoryId int,itemId int)
INSERT INTO #CategoryItem(categoryId,itemId) values
(5,1),(4,2),(5,2),(3,1),(2,3),(1,1),(3,2)

create table #Item(Id int identity(1,1),Name varchar(255))
INSERT INTO #Item(Name) values('item1'),('item2'),('item3')

Checking for all childs of parent by Recursive Commom Table Expressions

;WITH CategorySearch(ID, parentId) AS
(
SELECT ID, ID AS ParentId FROM #Category
UNION ALL
SELECT CT.Id,CS.parentId  FROM #Category CT
INNER JOIN CategorySearch CS ON CT.ParentId = CS.ID
)
select * from CategorySearch order by 1,2

Output: All child records against parent

ID  parentId
1   1
2   1
3   1
4   1
5   1
2   2
3   2
5   2
3   3
5   3
4   4
5   5

Final query for your result, count all items for category and its children categories.

;WITH CategorySearch(ID, parentId) AS
(
SELECT ID, ID AS ParentId FROM #Category
UNION ALL
SELECT CT.Id,CS.parentId  FROM #Category CT
INNER JOIN CategorySearch CS ON CT.ParentId = CS.ID
)
SELECT CA.Name AS CategoryName,count(itemId) CountItem
FROM #Category CA 
INNER JOIN CategorySearch CS ON CS.ParentId = CA.id
INNER JOIN #CategoryItem MI ON MI.CategoryId =CS.ID
GROUP BY CA.Name

Output:

CategoryName    CountItem
Category1           7
Category1.1         5
Category1.2         1
Category2.1         4
Category3.1         2