Compare Values from two MySQL tables

Question

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?

Answer 1

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.