Dealing with Parent-Child SQL Queries Pt2

Posted on January 14, 2011

1





In the previous post I showed how to use a recursive CTE to drill up and down a parent-child hierarchy. How to use recursive CTEs has been shown on many other blogs, but I haven’t seen any blogs showing how to join the results of the recursive CTE to connect to a fact table e.g. to get the roll up of sales qty by product.
Here’s an example of getting the results of all levels:

IF OBJECT_ID('dbo.Product') IS NOT NULL DROP TABLE dbo.Product
GO
CREATE TABLE dbo.Product(ProductID int not null
, ProductName varchar(100)
, ParentProductID INT)
GO
ALTER TABLE dbo.Product ADD CONSTRAINT
 PK_Product PRIMARY KEY CLUSTERED
 (
 ProductID
 ) ON [PRIMARY]
GO
INSERT INTO dbo.Product(ProductID, ProductName, ParentProductID)
VALUES (0,'Baby Goods',NULL)
, (10,'Baby Food',0)
, (20,'Nappies',0)
, (100,'All Ages Baby Food',10)
, (150,'Beginners',10)
, (200,'Strawberry Yoghurt',100)
, (250,'Baby Cereal',100)
, (300,'Formula Milk',150)
, (310,'Heinz Formula',300)
, (400,'Small Pack Nappies',20)
, (450,'Bulk Pack Nappies',20)
GO
IF OBJECT_ID('dbo.ProductSale') IS NOT NULL DROP TABLE dbo.ProductSale
GO
CREATE TABLE dbo.ProductSale(SaleDate DATE, ProductID INT, SaleQty INT)
GO
INSERT INTO dbo.ProductSale
VALUES (GETDATE(), 200, 50)
, (GETDATE(),250, 90)
, (GETDATE(), 310, 80)
, (GETDATE(), 400, 23)
, (GETDATE(), 450, 19)
GO
-- Get totals
;WITH CTE
As 
( SELECT  p.ProductName
       , p.ProductID
       , ParentProductID
       , ps.SaleQty
 FROM dbo.Product p  
 LEFT OUTER JOIN dbo.ProductSale ps
 ON ps.ProductID = p.ProductID
 WHERE ParentProductID IS NOT NULL
 UNION ALL
 SELECT p.ProductName
       , p.ProductID
       , p.ParentProductID
       , H.SaleQty
 FROM dbo.Product p  
 INNER JOIN CTE H
 ON H.ParentProductID=p.ProductID  
)  
SELECT c.ProductName, c.ProductID, SUM(SaleQty)  AS SaleQty
FROM CTE c 
WHERE SaleQty IS NOT NULL
GROUP BY c.ProductName, c.ProductID

This returns:

Product Name

ProductID

SalesQty

Baby Goods

0

262

Baby Food

10

220

Nappies

20

42

All Ages Baby Food

100

140

Beginners

150

80

Strawberry Yoghurt

200

50

Baby Cereal

250

90

Formula Milk

300

80

Heinz Formula

310

80

Small Pack Nappies

400

23

Bulk Pack Nappies

450

19

 

If we wish to see the results of Baby Food and its descendants then we need to use two CTEs – one to get the required products, the next to sum up the sales quantities for those products:

  

;WITH Descendants
AS
( SELECT  p.ProductName
      , p.ProductID
      , p.ParentProductID
 FROM dbo.Product p  
 WHERE ProductName = 'Baby Food'
 UNION ALL
 SELECT p.ProductName
      , p.ProductID
      , P.ParentProductID
 FROM dbo.Product p  
 INNER JOIN Descendants H
 ON H.ProductID=p.ParentProductID  
)  
, CTE AS
( SELECT  p.ProductName
      , p.ProductID
      , ParentProductID
      , ps.SaleQty
 FROM Descendants p  
 LEFT OUTER JOIN dbo.ProductSale ps
 ON ps.ProductID = p.ProductID
 UNION ALL
 SELECT p.ProductName
      , p.ProductID
      , p.ParentProductID
      , H.SaleQty
 FROM Descendants p  
 INNER JOIN CTE H
 ON H.ParentProductID=p.ProductID  
)  
SELECT ProductName, c.ProductID,  SUM(SaleQty)  AS SaleQty
FROM CTE c 
WHERE SaleQty IS NOT NULL
GROUP BY c.ProductName, c.ProductID


This returns:

ProductName

ProductID

SaleQty

Baby Food

10

220

All Ages Baby Food

100

140

Beginners

150

80

Strawberry Yoghurt

200

50

Baby Cereal

250

90

Formula Milk

300

80

Heinz Formula

310

80

 

  

 

Advertisements
Posted in: SQL