common table expression for parent child relationship with total
My knowledge on common table expressions is lacking at the moment so any help on this is greatly appreciated.
I have three tables [Category], [Product] and [Mapping] which defines relationships between Product and Category (one to many). Each Category has a ParentId which specifies its parent Category.
What I want to achieve is a list that includes each row from the Category table as well as the count of products assigned to it and all of its children.
So for example (Category table data)
Category ID 1 (ParentID 0) has 1 product Category ID 2 (ParentID 1) has 2 products Category ID 3 (ParentID 2) has 3 products
The result i'm after is something like this...
ID ParentID Products 1 0 6 2 1 5 3 2 3
Again any help on this would be fantastic!
Ok, assuming that you are using SQL Server 2005+ (since you are talking about CTEs), try this query:
WITH CTE1 AS ( SELECT A.ID, A.ParentID, COUNT(*) Products FROM Category A INNER JOIN Mapping B ON A.ID = B.CategoryID GROUP BY A.ID, A.ParentID ), CTE2 AS ( SELECT * FROM CTE1 UNION ALL SELECT B.ID, B.ParentID, A.Products FROM CTE2 A INNER JOIN CTE1 B ON A.ParentID = B.ID ) SELECT ID, ParentID, SUM(Products) Products FROM CTE2 GROUP BY ID, ParentID OPTION(MAXRECURSION 0)
You can see an example on this sql fiddle. The OPTION(MAXRECURSION 0) is there so it iterates as much levels as it can, so you either make sure that your data doesn't loop infinitly or you limit the levels of recursion.