I have below situation
Table 1
Id Sum
1 100
2 100
3 200
4 400
Table 2
ID Sum
5 100
6 200
7 300
8 400
I want to write a query that joins between both tables and show me the matching one time only. The result should be
1 100 Match
2 100 Mismatch
3 200 Match
4 300 Mismatch
5 400 Match
I am using this query
select a.id, a.sum,b.id,b.sum,
CASE
when a.sum = b.sum then 'Match'
else 'Mismatch'
end as Result
from table1 a LEFT OUTER join table2 b on a.sum = b.sum
The result is
1 100 5 100 Match
2 100 5 100 Match
3 200 6 200 Match
4 400 8 400 Match
Basically, this is a collection system. The system is saving the data automatically in table 2. When the employees do the real collection, they upload the data in table 1. Now I want to show a report that is showing the differences between collections based on the sum. So, if Employee inserted 100$ 2 times, I should show that the system captured this only once and there is 1 extra row in table 1
Are you looking to join on id?
select
t1.id,
t1.sum,
case when t1.sum = t2.sum then 'Match' else 'Mismatch' end as result
from table1 t1
inner join table2 t2 on t2.id = t1.id
Alternatively, if ids do not math, you might want to use row_number():
select
t1.id id1,
t1.sum sum1,
t2.id id2,
t2.sum sum2,
case when t1.sum = t2.sum then 'Match' else 'Mismatch' end as result
from (select t1.*, row_number() over(order by id) rn from table1 t1) t1
inner join ((select t2.*, row_number() over(order by id) rn from table2 t2) t2
on t2.rn = t1.rn
Finally: if the count of rows in both table might be different, use a full join instead of an inner join (if your database supports it):
select
t1.id id1,
t1.sum sum1,
t2.id id2,
t2.sum sum2,
case when t1.sum = t2.sum then 'Match' else 'Mismatch' end as result
from (select t1.*, row_number() over(order by id) rn from table1 t1) t1
full join (select t2.*, row_number() over(order by id) rn from table2 t2) t2
on t2.rn = t1.rn