Mysql doesn't work anyway

Question

So, I save all transactions in the transaction table with the following line:

+----------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------+------+-----+---------+----------------+ | debit_amount | decimal(10,2) | YES | | 0.00 | | | credit_amount | decimal(10,2) | YES | | 0.00 | | | flag | int(11) | YES | | NULL | | | date | datetime | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | +----------------+---------------+------+-----+---------+----------------+ 

Then I save the total number of credits that the user has in the "credits" line in the user table.

I’m trying to find out if there is a discrepancy in the total amount (debit amount + loan amount) for each user stored in the transaction table to the number of loans stored in the user table.

mainly for each user

  transactions.debit_amount + transactions.credit amount MUST EQUAL user.credits 

but an operator that is not equal to the operator in the mysql query does not work (especially when the value of transaction.total is null, i.e. there is no row in the transaction table for this user):

 SELECT s.id AS uid, s.total, s.credits FROM ( SELECT (sum(t.credit_amount) + sum(t.debit_amount)) AS total, t.userid, u.credits, u.id FROM transactions AS t RIGHT JOIN users AS u ON t.userid = u.id GROUP BY u.id ) AS s WHERE s.total != s.credits 

Answer 1

Try:

 select u.id, u.credits, t.total from users u left join ( select userid, sum(coalesce(credit_amount,0)) + sum(coalesce(debit_amount, 0)) as total from transactions group by userid ) t on u.id = t.userid where coalesce(t.total, 0) <> coalesce(u.credits, 0) 

Answer 2

You cannot compare NULL with a non-zero value in MySQL (or at least, if so, the result is always NULL ).

If you succeed, use the INNER JOIN to retrieve only the users who completed the transaction. If not, use COALESCE to provide a default value of 0 if there are no transaction rows, according to Michał's answer.