Martin AJ Martin AJ - 2 months ago 6
MySQL Question

How can I exclude posts whose total votes are less than 1?

Here is my query:

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.author_id = ?
AND question.amount IS NULL
AND answer.date_time >= UNIX_TIMESTAMP(now() - INTERVAL 1 YEAR) AND UNIX_TIMESTAMP(now() - INTERVAL 1 hour)
AND answer.id NOT IN (
SELECT post_id
FROM votes
WHERE table_code = 15
GROUP BY post_id
HAVING SUM(value) < 0 )


My query returns the number of user's answers that have either zero or a positive total votes (total votes: 0, 1, 2, ...). Now I need to exclude the answers that have
0
total votes.

therefore I replace:

... HAVING SUM(value) < 0


with

... HAVING SUM(value) < 1


But it doesn't work as expected. I mean it still counts the answers that have zero total votes. What's wrong? and how can I fix it?

Answer

The problem is your inner query doesn't select posts that have no votes, so they aren't excluded. There are a couple of ways of solving this, but the easiest is to reverse the logic from exclusion to inclusion, ie "show posts that have total votes > 1":

SELECT count(1)
 FROM qanda question
 JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
  AND answer.author_id = ?
  AND question.amount IS NULL
  AND answer.date_time >= UNIX_TIMESTAMP(now() - INTERVAL 1 YEAR) AND UNIX_TIMESTAMP(now() - INTERVAL 1 hour)
  AND answer.id IN ( -- remove "NOT"
      SELECT post_id
      FROM votes
      WHERE table_code = 15
      GROUP BY post_id
      HAVING SUM(value) > 0 ) -- change to > 0
Comments