3

Here is sqlFiddle for my database and my count query...

http://sqlfiddle.com/#!2/45150/6

If I run a select * query, it returns me one row, however when I run count on the same query, it gives me 2 results...

This is the the query for count, it produces different result for "select *"

SELECT count(*)
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`
Improve this question
2
  • select * returns 2 rows for me: sqlfiddle.com/#!2/45150/22 Commented Nov 29, 2013 at 4:34
  • 1
    @cha because you didn't put group by users.id Commented Nov 29, 2013 at 4:35

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

That happens because you're grouping by the user id.

If you want to run a select * then you will have to remove the group by clause. That would return the 2 results that the count(*) is returning. Those results happen to have the same id so they are grouped into only one result.

Edit after radical question change:

In a comment the OP mentioned:

I want to look for any user with more than one categories (coming from search)

That means you can't just blindly join on the categories table. You have to first check whether the user has more than one category assigned (that matches cat_id IN (1, 3, 4)). You can do that this way:

SELECT * FROM user_info ui
JOIN `users` u ON u.id = ui.user_id
LEFT JOIN profile p ON u.id = p.user_id
JOIN (
  SELECT user_id FROM categories
  WHERE cat_id IN (1, 3, 4) 
  GROUP BY user_id
  HAVING COUNT(*) > 1
) c ON c.user_id = u.id
WHERE u.is_enabled = 1 AND ( 
  ui.first_name LIKE '%bob%' OR
  ui.last_name LIKE '%bob%' OR
  p.title LIKE '%bob%' OR
  p.overview LIKE '%bob%'  
)

Fiddle here.

Improve this answer

7 Comments

but I want to see only one user? Each user has more than one categories selected in categories table... i don't want to repeat them? for example, search can be for categories id 1, 2, 3 and user 1 has 1, 2, 3 in table, it will show me 3 times user 1, I want to disply him only once???
I've explained why the wrong Count result is not wrong. If you need a particular output then you should add that to the question. Anyway, if you want to only display the user in one row... then what category id will contain that row? I can see in the query you added that the user has cat_id 4 and 1... which one would you choose to display in that row and why?
Appreciate that.. but you see, in the query I have JOIN categories ON categories.user_id = users.id AND categories.cat_id IN (1, 3, 4) ........ and cat_id is coming from array... which is dublicating the rows because of having more than one categories assigned to each user. I am getting correct results the problem is with count, what can I do to make it right?
You said I want to look for any user with more than one categories (coming from search). The cat_id in (1, 3, 4) will not give you the results you're looking for. If it only matches 1 it will return it but you said with more than one categories so it shouldn't
Welcome. But bear in mind that the cha's answer will return users matching ANY of those three categories. If you need to have them match more than one then you will have to use the HAVING COUNT(*) > 1
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.