# 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...

• Edited by Saturday, March 13, 2010 10:26 PM ss
Saturday, March 13, 2010 10:25 PM

• 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
WHERE  Color = 'Red'),
b
AS (SELECT ProductNumber,
ListPrice,
Color
WHERE  Color = 'Blue'),
c
AS (SELECT ProductNumber,
ListPrice,
Color
WHERE  Color = 'Yellow'),
d
AS (SELECT p.ProductNumber,
p.ListPrice,
p.Color,
bProductNumber = bsub.ProductNumber,
bColor = bsub.Color
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
*/```

Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
• Proposed as answer by Sunday, March 14, 2010 1:35 AM
• Marked as answer by Sunday, March 14, 2010 2:44 AM
Sunday, March 14, 2010 12:41 AM

### All replies

• 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
WHERE  Color = 'Red'),
b
AS (SELECT ProductNumber,
ListPrice,
Color
WHERE  Color = 'Blue'),
c
AS (SELECT ProductNumber,
ListPrice,
Color
WHERE  Color = 'Yellow'),
d
AS (SELECT p.ProductNumber,
p.ListPrice,
p.Color,
bProductNumber = bsub.ProductNumber,
bColor = bsub.Color
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
*/```