Recursive Query using CTE in SQL Server 2005

Question

OK, here's what I'm trying to do. I'm using a CTE query in MSSQL2005. The objective of the query is to recurse through Parent child relationships of product categories and return the number of products under each category (this includes any products contained in children categories)

My current version only returns the product count for the category being displayed. It's not accounting for products that may be contained within any of its children.

The database dump to reproduce the problem, along with the query I used and explanation follows below:

    CREATE TABLE [Categories] (
   [CategoryID] INT,
   [Name] NCHAR(150)

    )
    GO

/* Data for the `Query_Result` table  (Records 1 - 5) */


INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (942, N'Diagnostic Equipment')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (943, N'Cardiology')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (959, N'Electrodes')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (960, N'Stress Systems')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (961, N'EKG Machines')
GO

CREATE TABLE [Categories_XREF] (
   [CatXRefID] INT,
   [CategoryID] INT,
   [ParentID] INT
)
GO


/* Data for the `Query_Result` table  (Records 1 - 5) */


INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (827, 942, 0)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (828, 943, 942)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (928, 959, 943)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (929, 960, 943)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (930, 961, 943)
GO


CREATE TABLE [Products_Categories_XREF] (
   [ID] INT,
   [ProductID] INT,
   [CategoryID] INT
)
GO


/* Data for the `Query_Result` table  (Records 1 - 13) */


INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252065, 12684, 961)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252066, 12685, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252067, 12686, 960)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252068, 12687, 961)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252128, 12738, 961)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252129, 12739, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252130, 12740, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252131, 12741, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252132, 12742, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252133, 12743, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252134, 12744, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252135, 12745, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252136, 12746, 959)
GO

CREATE TABLE [Products] (
   [ProductID] INT
)
GO


/* Data for the `Query_Result` table  (Records 1 - 13) */


INSERT INTO [Products] ([ProductID])
VALUES (12684)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12685)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12686)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12687)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12738)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12739)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12740)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12741)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12742)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12743)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12744)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12745)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12746)
GO

Here's the CTE query I was using:

WITH ProductCategories (CategoryID, ParentID, [Name], Level)
AS
(
-- Anchor member definition
   SELECT
   C.CategoryID,
   CXR.ParentID,
   C.Name,
   0 AS Level
  FROM
  Categories C,
  Categories_XRef CXR
  WHERE
  C.CategoryID = CXR.CategoryID
  AND CXR.ParentID = 0
  UNION ALL
-- Recursive member definition
SELECT
   C.CategoryID,
   CXR.ParentID,
   C.Name,
   Level + 1
  FROM
  Categories C,
  Categories_XRef CXR,
  ProductCategories AS PC
  WHERE
  C.CategoryID = CXR.CategoryID 
  AND CXR.ParentID = PC.CategoryID

)
SELECT 
    PC.ParentID, 
    PC.CategoryID, 
    PC.Name, 
    PC.Level,
    (SELECT 
        Count(P.ProductID) 
     FROM 
        Products P,
        Products_Categories_XREF PCXR 
      WHERE 
       P.ProductID = PCXR.ProductID
       AND PCXR.CategoryID = PC.CategoryID
      ) as ProductCount
FROM     
    Categories C,
    ProductCategories  PC
WHERE
 PC.CategoryID = C.CategoryID
 AND PC.ParentID = 943
ORDER BY 
    Level, PC.Name

First, change the "PC.ParentID" to 943. You'll see three records returned showing the product Count for each category being displayed.

Now, change the ParentID from 943 to 942 and re-run it. You'll now see 1 result returned called "Cardiology", but it shows 0 products Under this category, there are children (who you previously saw) who contain products. My big question is, at this level (Parent 942) how can I make it count the products contained in the children below to show 13 as the "ProductCount" I'm kinda thinking I may need one more recursion method. I tried that, but had no success.

I'm open to a stored procedure that would do what I'm looking for. I'm not set on one particular way. So any other suggestions would be appreciated.

Answer 1

edit OK having actually read the requirements and thought a bit this is actually quite easy (I think!)

The point is that we want two things: the category hierarchy, and a count of products. The hierarchy is done by a recursive CTE, and counting is done outside that:

-- The CTE returns the cat hierarchy:
-- one row for each ancestor-descendant relationship
-- (including the self-relationship for each category)
WITH CategoryHierarchy AS (
    -- Anchor member: self relationship for each category
    SELECT CategoryID AS Ancestor, CategoryID AS Descendant
    FROM Categories
UNION ALL
    -- Recursive member: for each row, select the children
    SELECT ParentCategory.Ancestor, Children.CategoryID
    FROM 
        CategoryHierarchy AS ParentCategory
        INNER JOIN Categories_XREF AS Children
        ON ParentCategory.Descendant = Children.ParentID
)
SELECT CH.Ancestor, COUNT(ProductID) AS ProductsInTree
-- outer join to product-categories to include 
-- all categories, even those with no products directly associated
FROM CategoryHierarchy CH
LEFT JOIN Products_Categories_XREF PC
ON CH.Descendant = PC.CategoryID
GROUP BY CH.Ancestor

The results are:

Ancestor    ProductsInTree
----------- --------------
942         13
943         13
959         9
960         1
961         3

I am indebted to this article by the inestimable Itzik Ben-Gan for getting my thinking kick-started. His book 'Inside MS SQL Server 2005: T-SQL Querying' is highly recommended.

Answer 2

Your WHERE statement limits the result to one parent. If you'd like to see all children below 942, specify 942 as the root in the CTE. For example:

WITH CTE (CategoryID, ParentID, [Name], [Level])
AS
(
  SELECT C.CategoryID, CXR.ParentID, C.Name, 0 AS Level
  FROM Categories C
  INNER JOIN Categories_XRef CXR ON C.CategoryID = CXR.CategoryID 
  WHERE CXR.CategoryID = 943
  UNION ALL
  SELECT C.CategoryID, CXR.ParentID, C.Name, Level + 1
  FROM Categories C
  INNER JOIN Categories_XRef CXR ON C.CategoryID = CXR.CategoryID 
  INNER JOIN CTE PC ON PC.CategoryID = CXR.ParentID
)
SELECT * FROM CTE

By the way, can categories can have multiple parents? If not, consider eliminating the Categories_XREF table and storing ParentID in the Categories table.