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

Question

Is it possible to perform a JOIN sub query against a Common Table Expression? If not, then can someone let me know how to perform what I am trying to do below? Examples would be excellent.

For example:

LEFT JOIN (              ;WITH [UserDefined]                 AS (SELECT *, -- Make sure we get only the latest revision.                         ROW_NUMBER() OVER (PARTITION BY [ID]                                                 ORDER BY [RevisionNumber] DESC) AS RN                     FROM [syn_Change])              SELECT [UserDefined].[ID]                 ,[UserDefined].[ChangeNumber]                 ,[UserDefined].[Usr_CoResponsibility]                 ,[UserDefined].[Usr_StarFlowStatus]              FROM [UserDefined]             WHERE (RN = 1)               ) [UserColumns] ON [UserColumns].[ChangeNumber] = [CTE].[ChangeNumber] 

Here is my full query:

;WITH CTE     AS (SELECT *, -- Make sure we get only the latest revision.             ROW_NUMBER() OVER (PARTITION BY [ItemID]                                     ORDER BY [RevisionNumber] DESC) AS RN         FROM [dw_Change])  SELECT [CTE].[ItemID]         ,[CTE].[ViewID]         ,[CTE].[FolderItemID]         ,[CTE].[RevisionNumber]         ,[CTE].[ChangeNumber]         ,[CTE].[Synopsis]         ,[CTE].[Description]         ,[CTE].[EnteredOn]         ,[CTE].[Responsibility]         --,[UserColumns].[Usr_CoResponsibility]         --,[UserColumns].[Usr_StarFlowStatus]         ,[CTE].[Status] -- This will display the human name on the front-end with code.         ,[Users].[F7] AS [ResponsibilityName]         ,[GroupName].[Name] AS [AppGroupName]         ,[AppName].[Name] AS [AppName] FROM CTE LEFT JOIN [S3] [Users] ON [Users].[F0] = [CTE].[Responsibility] LEFT JOIN (SELECT [Name], [ViewID]             FROM [dw_Folder]             WHERE ([FolderItemID] = -1)) [GroupName] ON [GroupName].[ViewID] = [CTE].[ViewID]  LEFT JOIN (SELECT [Name], [ItemID]             FROM [dw_Folder]             WHERE ([FolderItemID] <> -1)) [AppName] ON [AppName].[ItemID] = [CTE].[FolderItemID]  LEFT JOIN (              ;WITH [UserDefined]                 AS (SELECT *, -- Make sure we get only the latest revision.                         ROW_NUMBER() OVER (PARTITION BY [ID]                                                 ORDER BY [RevisionNumber] DESC) AS RN                     FROM [syn_Change])              SELECT [UserDefined].[ID]                 ,[UserDefined].[ChangeNumber]                 ,[UserDefined].[Usr_CoResponsibility]                 ,[UserDefined].[Usr_StarFlowStatus]              FROM [UserDefined]             WHERE (RN = 1)               ) [UserColumns] ON [UserColumns].[ChangeNumber] = [CTE].[ChangeNumber]  WHERE (RN = 1) 

Thanks so much!

Answer 1

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

Answer 2

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.