Using CROSS APPLY to use the results of a previous CTE

Question

I don't even know if this is legal to do: say you have a set of nested CTEs: 

WITH a AS 

(

SELECT blah blah complex thing

), b AS

(

SELECT blah blah complex thing

), c AS

(

SELECT blah blah complex thing

), d AS 

(

SELECT blah blah complex thing 

CROSS JOIN (SELECT some column from "b" CTE above) 

)

SELECT final select

Anyone ever do such a thing? I'm wondering how you would select the right row from "b" CTE in the CROSS APPLY that's appropriate to the current row in "d" (the CTE with the CROSS APPLY)? Is there a join-like syntax for CROSS APPLY like the ON statement?

Thanks; I'm treading water in the deep end here...

  

Answer 1

Affirmative. You can do some pretty powerful queries with nested CTEs. Plus recursion!

Demo follows with CROSS APPLY along the line you are thinking. Let us now if helpful.

WITH a 
     AS (SELECT ProductNumber, 
                ListPrice, 
                Color 
         FROM   AdventureWorks2008.Production.Product 
         WHERE  Color = 'Red'), 
     b 
     AS (SELECT ProductNumber, 
                ListPrice, 
                Color 
         FROM   AdventureWorks2008.Production.Product 
         WHERE  Color = 'Blue'), 
     c 
     AS (SELECT ProductNumber, 
                ListPrice, 
                Color 
         FROM   AdventureWorks2008.Production.Product 
         WHERE  Color = 'Yellow'), 
     d 
     AS (SELECT p.ProductNumber, 
                p.ListPrice, 
                p.Color, 
                bProductNumber = bsub.ProductNumber, 
                bColor = bsub.Color 
         FROM   AdventureWorks2008.Production.Product p 
                CROSS APPLY (SELECT ProductNumber, 
                                    Color 
                             FROM   b 
                             WHERE  p.ProductNumber = b.ProductNumber) bsub 
         WHERE  p.Color IN ('Red','Black','Blue')) 
SELECT * 
FROM   d 
/*
ProductNumber	ListPrice	Color	bProductNumber	bColor
HL-U509-B	34.99	Blue	HL-U509-B	Blue
VE-C304-S	63.50	Blue	VE-C304-S	Blue
VE-C304-M	63.50	Blue	VE-C304-M	Blue
VE-C304-L	63.50	Blue	VE-C304-L	Blue
FR-T98U-46	1003.91	Blue	FR-T98U-46	Blue
FR-T98U-50	1003.91	Blue	FR-T98U-50	Blue
FR-T98U-54	1003.91	Blue	FR-T98U-54	Blue
FR-T98U-60	1003.91	Blue	FR-T98U-60	Blue
FR-T67U-50	333.42	Blue	FR-T67U-50	Blue
FR-T67U-54	333.42	Blue	FR-T67U-54	Blue
FR-T67U-58	333.42	Blue	FR-T67U-58	Blue
FR-T67U-62	333.42	Blue	FR-T67U-62	Blue
FR-T67U-44	333.42	Blue	FR-T67U-44	Blue
BK-T44U-60	1214.85	Blue	BK-T44U-60	Blue
BK-T18U-54	742.35	Blue	BK-T18U-54	Blue
BK-T18U-58	742.35	Blue	BK-T18U-58	Blue
BK-T18U-62	742.35	Blue	BK-T18U-62	Blue
BK-T79U-46	2384.07	Blue	BK-T79U-46	Blue
BK-T79U-50	2384.07	Blue	BK-T79U-50	Blue
BK-T79U-54	2384.07	Blue	BK-T79U-54	Blue
BK-T79U-60	2384.07	Blue	BK-T79U-60	Blue
BK-T44U-46	1214.85	Blue	BK-T44U-46	Blue
BK-T44U-50	1214.85	Blue	BK-T44U-50	Blue
BK-T44U-54	1214.85	Blue	BK-T44U-54	Blue
BK-T18U-44	742.35	Blue	BK-T18U-44	Blue
BK-T18U-50	742.35	Blue	BK-T18U-50	Blue
*/

CROSS APPLY article link: http://www.sqlusa.com/articles2005/crossapply/

---

Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com

Answer 2

Thanks for the examples. Just what I needed to know!