How to compare Date and values of two different table

Question

I have two tables that contain the same column name, the PMS will be varied every day, so both the table date, emp. code, and PMS are required to compare. If both tables have different PMS values on the employee on the same date, I require the output mentioned.

Table 1

ID	Date	emp. code	PMS
1	2023-12-01	V1856	5
2	2023-12-10	V2584	4
3	2023-12-15	V3643	5
4	2023-12-15	V1856	5
5	2023-12-16	V2584	6

Table 2

ID	Date	emp. code	PMS
1	2023-12-01	V1856	4
2	2023-12-10	V2584	4
3	2023-12-15	V3643	5
4	2023-12-15	V1856	5
5	2023-12-16	V2584	2

Output Required

ID	Date	emp. code	tbl1_PMS	tbl2_PMS
1	2023-12-01	V1856	5	4
2	2023-12-16	V2584	6	2

pls, suggest the MySQL code.

Answer 1

You can achieve this using INNER JOIN to join both tables then select unmatched rows :

select t1.ID, t1.Date, t1.emp_code, t1.PMS as tbl1_PMS, t2.PMS as tbl2_PMS 
from table1 t1
inner join table2 t2 on t1.Date = t2.Date
                    and t1.emp_code = t2.emp_code
where t1.PMS <> t2.PMS

Results :

ID  Date        emp_code    tbl1_PMS    tbl2_PMS
1   2023-12-01  V1856       5           4
5   2023-12-16  V2584       6           2

Demo here