T-SQL JOIN against a Common Table Expression (CTE)

When you define a CTE you’re doing so before any of the rest of the query. So you can’t write:

LEFT JOIN (
  ;WITH CTE
  ...
)

As a quick aside, the reason people put ; in front of WITH is because all previous statements need to be terminated. If developers could get in the habit of terminating all SQL statements with ; then it wouldn’t be necessary, but I digress…

You can write multiple CTEs like so:

WITH SomeCTE AS (
  SELECT ...
  FROM ...
), AnotherCTE AS (
  SELECT ...
  FROM ...
)
SELECT *
FROM SomeCTE LEFT JOIN
     AnotherCTE ON ...
;