Problem using mysql connections

Question

I am new to mysql and I have no idea if I am moving in the right direction, but I am having problems with mysql query.

I basically have a user table

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

as well as user attribute table

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

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

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

I tried to use such a connection.

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 this will not work, what is the best way to do this?

Answer 1

One way to do this is 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

( , MS SQL, , mysql ):

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 2

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 3

:

, 5 6, , .

SELECT a.id, u.name
FROM attributes AS a
LEFT JOIN users AS u ON a.id = u.id
WHERE a.attribute = 5 OR a.attribute = 6

Answer 4

, , .

:

, 5 6

- :

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

Answer 5

, SQL, , GROUP BY HAVING :) : http://www.w3schools.com/sql/sql_having.asp