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!

Answers


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.


Need Your Help

Is there a javascript calendar that takes an ical link as input to display events?

javascript jquery calendar icalendar

I am looking for a javascript based calendar that will allow me to specify a remote ical file to use as the source for the events.

Variable match in several worksheets

excel-vba vba excel

In my machineconfigurator I have the following code below which worked fine when I had all data for the machines in the same worksheets as the ComboBox2, but NOW I have reorganized the data and div...