Join and multiple and conditions

问题:

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..

回答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

回答2:

having clause can be used with sum

SELECT u.id FROM user u 
INNER JOIN attributes a ON u.id = a.user 
group by u.id
having ( sum(case when attribute in (1,2) then 1 else 0 end) ) =2

回答3:

You are looking for all users for whom EXIST attribute 1 and 2. One way to solve this - as the name suggests - is the EXISTS clause:

select * 
from users u
where exists
(
  select *
  from user_attributes ua
  where ua.user = u.id
  and ua.attribute = 1
)
and exists
(
  select *
  from user_attributes ua
  where ua.user = u.id
  and ua.attribute = 2
);

Another way is this: Find all user ids that have both attributes, then select from users table.

select *
from users
where id in
(
  select user
  from user_attributes
  where attribute in (1,2)
  group by user
  having count(*) = 2 
);

In case there are duplicate entries in attributes, you would have to replace count(*) with count(distinct attribute).

There are other ways to approach this. I think these two mentioned are rather straight-forward.

回答4:

You'll need a group by .... having

SELECT u.name
FROM
    users u
JOIN
    attributes a
    ON u.id = a.user
WHERE a.id IN (1,2)
GROUP BY u.name
    HAVING COUNT(*) = 2