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

Answer 2

Use CASE:

SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS `flag`
FROM `comments`
WHERE `comment_date` >= 1306904400 AND `user_id` = 1

Answer 3

You don't need to count all the matching records, just find if one exists. Unfortunately, MySQL doesn't appear to support IF EXISTS as Microsoft SQL Server does. However, you can use a subselect with LIMIT to fake it:

SELECT COUNT(1) AS has_comments FROM (SELECT id FROM comments WHERE comment_date >= 1306904400 AND user_id = 1 LIMIT 0, 1) t;

Answer 4

I know this is an old thread, but here's another way to accomplish this:

SELECT COUNT(id), 1 % (1 + count(id)) as greaterThanZero FROM comments
WHERE comment_date >= 1306904400 AND user_id = 1

Answer 5

I use a LIMIT to do something similar, and just evaluate it in my code. The SQL will stop after finding 10 matches.

SELECT id FROM {mytable}
WHERE comment_date >= 1306904400
AND user_id = 1
LIMIT 10

PHP requesting a minimum of 10 posts.

if(my_db_query() != 10)
    not_enough_posts();

Answer 6

perhaps:

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