Find mismatching rows

Transact-SQL
1

Hello,
I have to extract mismatching situazions betweens monetary values from 2 tables.
At the moment I have this query:

SELECT
(SELECT SUM(monValue) FROM TableA WHERE PK = 'Pk123' GROUP BY PK)

(SELECT monValue FROM TableB WHERE PK = 'Pk123')

for several hundreds of PkXXX.
If they match, this SELECT returns zero.

But I need to modify it to return me only when there is mismatching,
with the value ok PK that mismatch (the mismatching value is not necessary).

Has anyone idea how to solve this problem?

Thanks a lot in advance.

Luigi

2

Does TableB only have one row per PK?
If so, something lige this might work for you:

select b.pk
      ,sum(a.monvalue) as tablea_monvalue
      ,avg(b.monvalue) as tableb_monvalue
  from tableb as b
       left outer join tablea as a
                    on a.pk=b.pk
 group by b.pk
 having avg(b.monvalue)<>isnull(sum(monvalue),0)
;
1 Like
3

Whether a single row or multiple, this code will check for non-matches from the sum of TableA to the max value of TableB.

SELECT A.*, B.*
FROM 
    (SELECT PK, SUM(monValue) AS monValue FROM TableA GROUP BY PK) AS A
    INNER JOIN 
    (SELECT PK, MAX(monValue) AS monValue FROM TableB GROUP BY PK) AS B 
    ON B.PK = A.PK AND B.monValue <> A.monValue
1 Like
4

Great, this work perfectly. Thank you very much.

5

This works too, another way.

Thanks a lot bitsmed.

Luigi