SELECT users who have downvoted but never upvoted

Question

So, I thought I was getting pretty good at MySQL until I ran into this idea:

I have a table logging "votes" (aptly named votes) with these fields:

• id: The vote's unique ID.
• user: Unique User ID of the person who voted
• item: ID of item they're voting on
• vote: The vote they cast SET('up','down')

Now, I'm trying to come up with an SQL way to find users whose only votes are downvotes. I know of a way to write it procedurally in php after querying most of the data out of the table but it seems really, really inefficient to do that way when only a few queries could find this out.

Ideally I want my result to just be a list of users who have 0 upvotes (as being in the table means they have voted, so they only downvote) and maybe the number of downvotes they've cast.

Any ideas on how I should approach this?

Answer 1

SELECT user, SUM(IF(vote='down',1,0)) AS numDownVotes
FROM votes
GROUP BY user
HAVING SUM(IF(vote='up',1,0))=0   -- 0 upvotes
   AND SUM(IF(vote='down',1,0))>0 -- at least 1 downvotes

I can't help but feel there's a neat GROUP BY user, vote way to do this though.

Answer 2

I haven't checked the syntax or anything, but this comes to mind...

SELECT user 
FROM votes 
GROUP BY user 
  HAVING SUM(IF(vote = 'up', 1, 0)) = 0 
     AND SUM(IF(vote = 'down', 1, 0)) > 0