CODEWITHSUNDEEP
mysql
ButtressCoral
ButtressCoral

Reputation: 113

Select where two column values are not equal

I'm looking to select rows where values from two columns are not equal.

I have the following MySQL query:

SELECT t1.id, t1.numvotes, COUNT(DISTINCT t2.id) AS numvotes
FROM t1
LEFT JOIN t2 ON
    (t1.id = t2.id)
GROUP BY t1.id

The data from t1 is:

  id    |   numvotes
 -------------------
  1     |   4
  2     |   6 
  3     |   1
  4     |   3
  5     |   2

The data from t2 is:

 id
-----
 1 
 1
 1
 1
 2
 2
 2
 3
 4
 4
 4
 5

The results from the above query would be:

  id    |  numvotes  |  numvotes
 -------------------------------
  1     |  4         |  4
  2     |  6         |  3
  3     |  1         |  1
  4     |  3         |  3
  5     |  2         |  1

Now, I want to modify the MySQL query to only SELECT where the numvotes from t1 is different from the calculated COUNT DISTINCT value from t2.

Upvotes: 1

Views: 3020

Answers (1)

fancyPants
fancyPants

Reputation: 51938

This is done in the HAVING clause. WHERE filters before GROUP BY, HAVING after GROUP BY.

SELECT t1.id, t1.numvotes, COUNT(DISTINCT t2.id) AS numvotes
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
GROUP BY t1.id
HAVING COUNT(DISTINCT t2.id) <> t1.numvotes

Upvotes: 3

Related Questions