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)
CodePudding user response:
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