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

SELECT u.id, u.name FROM users u
  INNER JOIN attributes a1 ON u.id = a1.userid
  INNER JOIN attributes a2 ON u.id = a2.userid
WHERE a1.attribute = 5 AND a2.attribute = 6

Answer 2

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

Answer 3

Based on your question, I don't think the other two current answers are satisfactory.

If you want to:

select users that have both the attribute 5 and the attribute 6

The following query is one way to accomplish that:

select
  *
from
  users
where
  id in (select userid from attributes where attribute = 5)
  and
  id in (select userid from attributes where attribute = 6)

Answer 4

Hmm, I am not much into SQL, maybe GROUP BY and HAVING will help you:) Check out the reference: http://www.w3schools.com/sql/sql_having.asp