CODEWITHSUNDEEP
phpmysql
user816604
user816604

Reputation:

mysql not equal not working

So I store all transactions in a transaction table with the following struture:

+----------------+---------------+------+-----+---------+----------------+
| 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 store the total amount of credits the user has in the "credits" row in the user table.

I am trying to figure out whether there is a mismatch in the total(debit amount + credit amount) for each user as stored in the transactions table to the number of credits stored in the user table.

basically for each user

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

but the does not equal operator in the mysql query does not work (especially when the transactions.total is null i.e. there is no row in the transactions table for that 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

Upvotes: 4

Views: 1361

Answers (2)

cmbuckley
cmbuckley

Reputation: 42458

You can't compare NULL to a non-null value in MySQL (or at least, if you do, the result is always NULL).

If you can get away with it, use an INNER JOIN to only get users who have made a transaction. If not, use COALESCE to provide a default value of 0 when there are no transaction rows, as per Michał's answer.

Upvotes: 0

Michał Powaga
Michał Powaga

Reputation: 23173

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)

Upvotes: 4

Related Questions