CODEWITHSUNDEEP
mysqldatabasejoinwhere-clause
KimCrab
KimCrab

Reputation: 2361

Using MySQL Count() with multiple conditions in different table

I have following tables.

User_Location TABLE

  User_id|location_id
  _______|____________
     1   |     3        
     2   |     2    
     3   |     1
     4   |     1

User_Hobby TABLE

  User_id|  Hobby_id
  _______|____________
     1   |     1        
     1   |     3      
     1   |     5
     1   |     7        
     1   |     9      
     2   |     2
     2   |     4        
     2   |     6      
     2   |     8
     2   |     10        
     3   |     1     
     3   |     2
     3   |     3        
     3   |     4      
     3   |     5
     4   |     2     
     4   |     4
     4   |     6        
     4   |     8      
     4   |     10

Each user has five hobbies and one location. I want to count the number of same hobbies. For example, If my hobby list is [1,2,3,4,5] and Location_id is 1, I need results like this.

  User_id|matched_hobby
  _______|____________
     3   |     5         ->[1,2,3,4,5] matched
     4   |     2         ->[2,4] matched

Any Idea about implementation?

********************* UPDATE *********************

Alex Martelli's answer works perfectly when there is only one location_id. What if there is a range of location_id in Tabel? ex)

  User_id|location_id
  _______|____________
     1   |     3        
     2   |     2      
     3   |     10
     3   |     11
     3   |     12
     4   |     50
     4   |     51
     4   |     52

SELECT uh.User_id, COUNT(uh.Hobby_id)

FROM User_Location ul JOIN User_Hobby uh USING (User_id)

WHERE (ul.Location_id >= 5 AND ul.Location_id <= 15)

AND uh.Hobby_id in (1, 2, 3, 4, 5)

GROUP BY (uh.User_id)

is not working. It returns [#matched_hobby]*[#location_id]

It shoud be 

  User_id|matched_hobby
  _______|____________
     3   |     5         ->[1,2,3,4,5] matched

Upvotes: 2

Views: 129

Answers (2)

Rohit Gaikwad
Rohit Gaikwad

Reputation: 815

try this

SELECT uh.User_id, COUNT(uh.Hobby_id)
FROM User_Location ul 
INNER JOIN User_Hobby uh ON ul.User_id = uh.User_id
WHERE uh.Hobby_id in (1, 2, 3, 4, 5)
AND (ul.Location_id >= 5 AND ul.Location_id <= 15)
GROUP BY uh.User_id, ul.Location_id

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 881575

Looks like

SELECT uh.User_id, COUNT(uh.Hobby_id)
   FROM User_Location ul JOIN User_Hobby uh USING (User_id)
   WHERE ul.Location_id = 1
     AND uh.Hobby_id in (1, 2, 3, 4, 5)
     GROUP BY (uh.User_id)

should give the results you're looking for.

Upvotes: 3

Related Questions