Current Method:

Current Query 1

SELECT
a.user,ui.contact_fname, ui.contact_lname,a.uid, COALESCE(pu.name,'N/A') AS UserPolicy,COALESCE(count(ea.user_ack),0) AS ack_count
FROM master_access.accounts a
RIGHT JOIN master_events.events_cleared ea ON a.uid=ea.user_ack
INNER JOIN master_biz.user_information as ui on ui.uid=a.uid
LEFT JOIN `master`.policies_users pu ON (a.template_id=pu.policy_id AND a.template_id!=0)
WHERE YEAR(ea.date_ack)=YEAR(NOW()) and MONTH(ea.date_ack)=10 and a.uid in (120,119,125,128,123,117,122,118,121,127)
GROUP BY a.user
ORDER BY COUNT(ea.user_ack) DESC

Current Query 1 - Output

enter image description here

Current Query 2

SELECT
a.user,ui.contact_fname, ui.contact_lname,a.uid, COALESCE(pu.name,'N/A') AS UserPolicy,COALESCE(count(ea.user_ack),0) AS ack_count
FROM master_access.accounts a
RIGHT JOIN master_events.events_active ea ON a.uid=ea.user_ack
INNER JOIN master_biz.user_information as ui on ui.uid=a.uid
LEFT JOIN `master`.policies_users pu ON (a.template_id=pu.policy_id AND a.template_id!=0)
WHERE YEAR(ea.date_ack)=YEAR(NOW()) and MONTH(ea.date_ack)=10 and a.uid in (120,119,125,128,123,117,122,118,121,127)
GROUP BY a.user
ORDER BY COUNT(ea.user_ack) DESC

Current Query 2 - Output

enter image description here

Desired Output

Basically adding the two outputs into one view, adding the values, if the "User ID" matches. Could someone please assist with this?

CodePudding user response：

Take master_access.accounts table as a base. LEFT JOIN both queries to it. Filter rows where at least one subquery row matches. Build output columns list.

SELECT *        -- build needed columns list
FROM master_access.accounts
LEFT JOIN ( {query 1 text} ) AS subquery1 USING (uid)
LEFT JOIN ( {query 2 text} ) AS subquery2 USING (uid)
WHERE COALESCE (subquery1.uid, subquery2.uid) IS NOT NULL

In the output list - take uid column without table alias rather than all another columns.