Wrox Programmer Forums
|
BOOK: Professional Microsoft SQL Server 2008 Programming
This is the forum to discuss the Wrox book Professional Microsoft SQL Server 2008 Programming by Robert Vieira; ISBN: 9780470257029
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional Microsoft SQL Server 2008 Programming section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 25th, 2010, 11:48 AM
Authorized User
 
Join Date: Jan 2009
Posts: 21
Thanks: 5
Thanked 0 Times in 0 Posts
Default 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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chap 2 forums, pagination issues page 55-56 kenj BOOK: PHP and MySQL: Create-Modify-Reuse ISBN: 978-0-470-19242-9 0 October 19th, 2009 09:28 PM
Hyperlink on page 55 rhoss BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 3 July 6th, 2008 01:12 PM
How to use CTE instead of temp table jayaraj123 SQL Server 2005 0 April 25th, 2007 08:03 PM
reference of prev. page in current page kasanar ASP.NET 1.0 and 1.1 Professional 1 February 13th, 2005 02:49 PM
Page 55. Working with 3 projects? BradDotNet BOOK: ASP.NET Website Programming Problem-Design-Solution 1 July 24th, 2003 12:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.