I want to fetch all distinct user_ids with an item_id=100 AND item_id=200. The correct result in this case would be 1. But how would my SQL-query look like in this simplified case? Table:
user_id item_id
1 100
1 200
3 100
Use conditional aggregation:
SELECT user_id
FROM yourTable
WHERE item_id IN (100, 200)
GROUP BY user_id
HAVING COUNT(DISTINCT item_id) = 2;
This trick first restricts to only records corresponding to your two items of interest, then it asserts that a matching user has a distinct item count of two, implying that he meets the criteria.
Sometimes logic requires doing this in a slightly different way:
SELECT user_id
FROM yourTable
GROUP BY user_id
HAVING
SUM(CASE WHEN item_id = 100 THEN 1 ELSE 0 END) > 0 AND
SUM(CASE WHEN item_id = 200 THEN 1 ELSE 0 END) > 0;
This form can be more useful in certain situations, but it does the same thing.
DROP TABLE IF EXISTS T;
CREATE table t(user_id int, item_id int);
insert into t values
(1 , 100),
(1 , 200),
(3 , 100);
select user_id
from t
where item_id in (100, 200)
group by user_id having count(distinct item_id) = 2;
+---------+
| user_id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
You can use self-join (intuitively easier to understand):
SELECT DISTINCT(t1.user_id)
FROM table_name AS t1 inner join table_name AS t2
ON t1.user_id=t2.user_id
WHERE t1.item_id=100 and t2.item_id=200;
DISTINCT will also require an aggregation step.