0

I have the following table:

ID  user_id          skill_id        
1   8                1    
2   8                3     
3   2                3        
4   5                3          
5   2                1          
6   5                2
7   5                4
8   5                5
9   8                5   

I need to get the count of users who have both skills 1 and 3 (or more), a real life example would be to get the count of users who know php,mysql,and javascript, but not those that only know php and javascript.

When I do SELECT COUNT(USER_ID) WHERE skill_id IN(1,3) I get a count of 3, which means it's giving me the users with any of them, I need the users who have all?

As a side note I think I could do this intersecting different queries but that would mean a separated query for each tag and there can be endless combinations of skills, something tells me there is a simpler way, speed is really important here.

Thanks.

0

To obtain those users use

SELECT user_id
FROM `table`
WHERE skill_id IN(1,3)
GROUP BY user_id
HAVING 2 = COUNT(*) 

If there is no unique constraint for (user_id, skill_id), use COUNT (DISTINCT skill_id) instead of COUNT(*).

To obtain only the count of those users you may use the above query as a subquery:

SELECT COUNT(*)
FROM ( SELECT user_id
       FROM `table`
       WHERE skill_id IN(1,3)
       GROUP BY user_id
       HAVING 2 = COUNT(*) 
     ) AS `table`
  • This almost works, it gives me the users that have all the selected skills but it doesn't give me a COUNT, SELECT COUNT(user_id) FROM `table` WHERE skill_id IN(1,3) GROUP BY user_id HAVING 2 = COUNT(*) gives me a lots of rows that have the same value, Also this means that if I want 3 skills I have to change it to HAVING 3 = COUNT(*) – ccrez Sep 24 '18 at 18:32
  • @ccrez answer updated. – Akina Sep 24 '18 at 18:38
  • Thank you that worked perfectly!, hope this ends up being helpful to other people, I up-voted your answer but it says it wont show cause I don't have enough reputation :( – ccrez Sep 24 '18 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.