I have some denormalized data in two tables. It's denormalized because from the DepositsAndWithdrawals table (second one below) I can get the current customer's balance, but for fast access I keep it in the UserBalance table (first one below) so I don't need to sum all of his operations all the time:
+----+----------+---------+ | ID | Username | Balance | +----+----------+---------+ | 1 | Alice | 50 | +----+----------+---------+ | 2 | Bob | 20 | +----+----------+---------+ | 3 | Charles | 30 | +----+----------+---------+
+----+------------+---------+ | ID | CustomerID | Amount | +----+------------+---------+ | 1 | 1 | 50 | +----+------------+---------+ | 2 | 2 | 80 | +----+------------+---------+ | 3 | 3 | 10 | +----+------------+---------+ | 4 | 2 | -60 | +----+------------+---------+ | 5 | 3 | 20 | +----+------------+---------+
This brings up a consistency problem in case there are race conditions in the system. So, in order to detect those, I'd like to have a daily DB query that detects balance inconsistencies (that is, when SUM(amount) is different from balance). How to do such a JOIN in a single SQL statement?
Try this
SELECT A.ID As customerId, A.BALANCE, SUM(B.AMOUNT) AS REAL_BALANCE FROM A
JOIN B on A.ID = B.customerId
GROUP BY A.ID, A.BALANCE
HAVING SUM(B.AMOUNT) <> A.BALANCE
Where B is your DepositsAndWithdrawals table and A is UserBalance table