Union All with Join

I’m trying to perform a join after Union All. Separately the 2 queries work but I haven’t been able to piece together the join. The Unioned query needs to then be joing by assigned_user_id from the accounts table join

(SELECT 
       c.[id]
      ,c.[assigned_user_id]
      ,c.[assigned_user_name]
  FROM DB.[Sales] AS s
  WHERE [IsCurrent] = 1

  UNION ALL 

SELECT 
       m.[id]
      ,m.[assigned_user_id]
      ,m.[assigned_user_name]
  FROM [DB].[Units] AS u
  WHERE [IsCurrent] = 1)


 (SELECT 
       a.[id]
      ,a.[assigned_user_name]
      ,u.[id] 
  FROM [DB].[Accounts] AS a
  JOIN [DB].[Users] AS u  ON a.[assigned_user_name] = u.[full_name]
  WHERE a.[IsCurrent] = 1 AND u.IsCurrent = 1)

Answer

You need to make them subqueries, like this:

SELECT * -- something here, not sure what you need
  FROM (SELECT s.[id]
              ,s.[assigned_user_id]
              ,s.[assigned_user_name]
          FROM DB.[Sales] AS s
          WHERE [IsCurrent] = 1

          UNION ALL 

        SELECT u.[id]
              ,u.[assigned_user_id]
              ,u.[assigned_user_name]
          FROM [DB].[Units] AS u
          WHERE [IsCurrent] = 1
     ) unioned
  JOIN (SELECT a.[id] as assigned_account_id 
             , a.[assigned_user_name]
             , u.[id] as assigned_user_id -- not sure if this is the column you want to join on
          FROM [DB].[Accounts] AS a
          JOIN [DB].[Users] AS u  ON a.[assigned_user_name] = u.[full_name]
         WHERE a.[IsCurrent] = 1 AND u.IsCurrent = 1
     ) account_user
    ON unioned.assigned_user_id = account_user.assigned_user_id

I personally don’t like subqueries this long and would have written it using CTEs:

WITH unioned AS (
    SELECT s.[id]
          ,s.[assigned_user_id]
          ,s.[assigned_user_name]
      FROM DB.[Sales] AS s
     WHERE [IsCurrent] = 1

      UNION ALL 

    SELECT u.[id]
          ,u.[assigned_user_id]
          ,u.[assigned_user_name]
      FROM [DB].[Units] AS u
     WHERE [IsCurrent] = 1
),
account_users AS (
    SELECT a.[id] as assigned_account_id 
         , a.[assigned_user_name]
         , u.[id] as assigned_user_id -- not sure if this is the column you want to join on
      FROM [DB].[Accounts] AS a
      JOIN [DB].[Users] AS u  ON a.[assigned_user_name] = u.[full_name]
     WHERE a.[IsCurrent] = 1 AND u.IsCurrent = 1
)
SELECT * -- something here, not sure what you need
  FROM unioned
  JOIN account_user
    ON unioned.assigned_user_id = account_user.assigned_user_id