Join and multiple and conditions

Question

I have users table

ID     NAME
1      John
2      Mike
3      Jack

and table with attributes and user IDs

USER   ATTRIBUTE
1      1
1      2
2      4

I need to select all users with attribute 1 AND 2 (so, in this example user #1 John). Attributes can be more than two.

I'v tried

SELECT * FROM user u LEFT JOIN attributes a ON u.id = a.user 
WHERE a.attribute = 1 AND a.attribute = 2

but of course it not working..

Answer 1

You will need to use a combination of IN() and GROUP BY ... HAVING to achieve this. Also no need for a join if all you need is user ID's. So something like:

SELECT user, COUNT(attribute) AS attribute_count
FROM attributes
WHERE attribute IN(...) /* include your set of attributes here */
GROUP BY user
HAVING attribute_count = ? /* include number equal to number of attribute ID's in IN() above */

If you need user id's and names you can simply join this record set derived from the query above as a filter to the users table:

SELECT user.id, user.name
FROM user
INNER JOIN
  (
    SELECT user, COUNT(attribute) AS attribute_count
    FROM attributes
    WHERE attribute IN(...) /* include your set of attributes here */
    GROUP BY user
    HAVING attribute_count = ? /* include number equal to number of attribute ID's in IN() above */
  ) AS filter
  ON user.id = filter.user