mySQL returns wrong Count result

mysql, sql

Solution

As you mentioned you can have multiple categories associated with a user. Therefore, using COUNT(*) is not possible in your situation. To fix it you can use either of the following options:

Quite simply, use SELECT COUNT(DISTINCT `users`.`id`), like in this SQL Fiddle:

SELECT count(distinct `users`.`id`)
FROM (`user_info`)
JOIN `users` ON `users`.`id`=`user_info`.`user_id`
LEFT JOIN `profile` ON `users`.`id`=`profile`.`user_id`
JOIN `categories` ON `categories`.`user_id` = `users`.`id`
WHERE `users`.`is_enabled` =  1
AND `categories`.`cat_id` IN (1, 3, 4) 
AND ( 
 user_info.first_name LIKE '%bob%'  
 OR user_info.last_name LIKE '%bob%' 
 OR profile.title LIKE '%bob%' 
 OR profile.overview LIKE '%bob%'  
 )
GROUP BY `users`.`id`

However, this will only hide the problem. The problem will reappear if you start adding new aggregate functions to your select statement (like SUM(salary), etc.)

The proper solution will be, I guess is to fix the duplication and modify your FROM clause, like this:

SELECT count(*)
FROM (`user_info`)
JOIN `users` ON `users`.`id`=`user_info`.`user_id`
LEFT JOIN `profile` ON `users`.`id`=`profile`.`user_id`
WHERE `users`.`is_enabled` =  1
AND ( 
 user_info.first_name LIKE '%bob%'  
 OR user_info.last_name LIKE '%bob%' 
 OR profile.title LIKE '%bob%' 
 OR profile.overview LIKE '%bob%'  
 )
AND `users`.`id` IN (SELECT `user_id` FROM `categories` WHERE `categories`.`cat_id` IN (1, 3, 4) )


GROUP BY `users`.`id`

SQL Fiddle

Problem

Original source