Keeping it simple and how to do multiple CTE in a query

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables.

My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event.

My solution to this is to add a CTE that groups scheduled events and counts the number of participants.

This will allow me to join in that information per scheduled event. Keeping the query simple.

I like to keep my queries simple, however, If I ever in the future need to have additonal temporary results accessible during my simple query, what do I do?

I would really like it, if I could have multiple CTEs but I can't, right? What are my options here?

I've ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.

2 Answers

You can have multiple CTEs in one query, as well as reuse a CTE:

WITH cte1 AS ( SELECT 1 AS id ), cte2 AS ( SELECT 2 AS id )
SELECT *
FROM cte1
UNION ALL
SELECT *
FROM cte2
UNION ALL
SELECT *
FROM cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

You certainly are able to have multiple CTEs in a single query expression. You just need to separate them with a comma. Here is an example. In the example below, there are two CTEs. One is named CategoryAndNumberOfProducts and the second is named ProductsOverTenDollars.

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
( SELECT CategoryID, CategoryName, (SELECT COUNT(1) FROM Products p WHERE p.CategoryID = c.CategoryID) as NumberOfProducts FROM Categories c
),
ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
( SELECT ProductID, CategoryID, ProductName, UnitPrice FROM Products p WHERE UnitPrice > 10.0
)
SELECT c.CategoryName, c.NumberOfProducts, p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p INNER JOIN CategoryAndNumberOfProducts c ON p.CategoryID = c.CategoryID
ORDER BY ProductName