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