CODEWITHSUNDEEP
mysqlsql
kimion09
kimion09

Reputation: 383

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

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?

Upvotes: 16

Views: 43023

Answers (6)

Jack
Jack

Reputation: 492

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

Upvotes: 1

Rucent88
Rucent88

Reputation: 950

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();

Upvotes: 0

Michael Dean
Michael Dean

Reputation: 1576

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;

Upvotes: 4

mu is too short
mu is too short

Reputation: 434655

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

Upvotes: 32

jcomeau_ictx
jcomeau_ictx

Reputation: 38422

perhaps:

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

Upvotes: 0

Xint0
Xint0

Reputation: 5389

Use CASE:

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

Upvotes: 16

Related Questions