MySQL.. Return '1' if a COUNT returns anything greater than 0

Question

I'm trying to create MySQL query that essentially returns true or false. I'd like to run

SELECT COUNT(id) 
  FROM comments  
 WHERE comment_date >= 1306904400 
   AND user_id = 1 

So if the user posted in the forum 10 times this month, I'd like it to return just 1, otherwise I'd like it to return 0 to indicate they haven't.

Is that possible efficiently within SQL?

Answer 1

If you don't mind MySQL-specific things then you could use IF:

select if(count(id) >= 10, 1, 0)
from comments
where comment_date >= 130690440
  and user_id = 1

Or MySQL booleans (which are 1 for true and 0 for false):

select count(id) >= 10
from comments
where comment_date >= 130690440
  and user_id = 1

If you want to stick to standard SQL, then CASE is your friend:

select case when count(id) >= 10 then 1 else 0 end
from comments
where comment_date >= 130690440
  and user_id = 1