title: Keeping it simple and how to do multiple CTE in a query
body: <p>I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other <em>related</em> tables.</p>
  
  <p>My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event.</p>
  
  <p>My solution to this is to add a CTE that groups scheduled events and counts the number of participants.</p>
  
  <p>This will allow me to join in that information per scheduled event. Keeping the query simple.</p>
  
  <p>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?</p>
  
  <p>I would really like it, if I could have multiple CTEs but I can't, right? What are my options here?</p>
  
  <p>I've ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.</p>