Problem using mysql joins

Question

I'm fairly new to mysql and I have no idea if I'm heading in the right direction but I'm having trouble with a mysql query.

I basically have a table of users

id      name  
----    -------- 
1       user1          
2       user2          
3       user3          
4       user4

as well as a table of user attributes

id      userid   attribute 
----    -----    ------ 
1       1        5          
2       1        6       
3       2        5          
4       3        4

I want to be able to select users that have both the attribute 5 and the attribute 6, so in this case I want to return

id      name  
----    -------- 
1       user1  

I tried using a join like this.

SELECT u.id, u.name FROM users u LEFT JOIN attributes a ON (a.userid = u.id) WHERE a.attribute = 5 AND a.attribute = 6

But obviously that won't work, what is the best way of doing this?

Answer 1

One way to do this would be to use two joins; eg:

SELECT ...
FROM users u
    JOIN attributes a5 ON u.id = a5.userid AND a5.attribute = 5
    JOIN attributes a6 ON u.id = a6.userid AND a6.attribute = 6

Another way is by grouping (note that I am a MS SQL person, not sure if this is the right syntax for mysql or not):

SELECT u.id, u.name
FROM users u
    JOIN attributes a ON u.id = a.userid
WHERE a.attribute IN (5,6)
GROUP BY u.id, u.name
HAVING COUNT(*) = 2