Check out this video explanation of how to put multiple CTE’s in a single query.
Check back tomorrow for the demo on this.
Transcription to this video:
Now that we’ve covered how to build a CTE and how to replace some derived tables or sub queries with CTE’s, now we’re going to look at how do we put multiple common table expressions into a query. So basically, you just have to build multiple CTE’s, and comma, separate them in a single query. Here’s an example of your first CTE, where you have the width, and then the name, and then a query inside of parentheses. And then you put a comma and a second CTE name, you don’t need the word with again, you just have the CTE name, the AZ and then the second query inside of parentheses. And this query does not have to be related to the first query in any way can have totally different tables, totally different columns, just just like two different derived table queries would be. And then you can select from one CTE and inner join to the second CTE on some value that you would join on or cross join or those types of things. So really, all you do is you add a comma at the end of the first CTE, after the comma on the next line, declare the name after the name. Add the optional columns if you want them, then add the as keyword and followed by opening and closing parentheses then inside the parentheses, add the new CTE query, and then call the CTE query from the outer SELECT statement. What’s interesting in that last statement, is that you don’t actually have to call the CTE from anywhere if you’re just testing and you can comment out references to different CTE’s without coming out, commented out the whole CTE and that CTE query won’t get executed in your testing.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!