Page 55, reference both using CTE
I have just started reading this book, and I really like it a lot!
For every page I read, I'm filling gaps/black holes in my brain:)
But I think that the author is wrong when he on page 55 says that one can't replace both derived tables with a CTE. Actually, you can!
Please take a look at the code below:
USE AdventureWorks2008;
WITH pumps(BusinessEntityID)
AS
(
SELECT sc.PersonID AS BusinessEntityID
FROM Sales.Customer sc
JOIN Sales.SalesOrderHeader AS soh ON sc.CustomerID = soh.CustomerID
JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p ON sod.ProductID = p.ProductID
WHERE p.Name ='Minipump'
),
awc (BusinessEntityID)
AS
(
SELECT sc.PersonID AS BusinessEntityID
FROM Sales.Customer sc
JOIN Sales.SalesOrderHeader AS soh ON sc.CustomerID = soh.CustomerID
JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p ON sod.ProductID = p.ProductID
WHERE p.Name ='AWC Logo Cap'
)
SELECT DISTINCT pp.FirstName, pp.LastName
FROM Person.Person AS pp
JOIN pumps ON pp.BusinessEntityID = pumps.BusinessEntityID
JOIN awc ON awc.BusinessEntityID = pp.BusinessEntityID
|