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

Multiple CTEs need to be declared first. Example:

WITH CTE_1 AS
(
    ....
),
CTE_2 AS
(
    ...
)

SELECT        *
FROM          FOO f
LEFT JOIN     CTE_1 c1 ON c1.[SomeCol] = f.[SomeCol]
LEFT JOIN     CTE_2 c2 ON c2.[SomeCol] = f.[SomeCol]

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

If you have multiple CTE's, they need to be at the beginning of your statement (comma-separated, and only one ;WITH to start the list of CTE's):

;WITH CTE AS (......),
 [UserDefined] AS (.......)
SELECT.....

and then you can use both (or even more than two) in your SELECT statement.