This is probably a simple SQL statement but right now I have a "brain freeze".
I have this table
variant_id | filter_id
1585593211 4
1585593211 48
1585593212 4
1585593212 49
I need to get variant_id that have filters_id: 4 and 48. In this case I expect to get 1585593211
You can use IN to get all rows with filter_id 4 or 48. Then GROUP BY variant_id and check, that the count of distinct filter_ids is equal to 2 in a HAVING clause.
SELECT variant_id
FROM elbat
WHERE filter_id IN (4, 48)
GROUP BY variant_id
HAVING count(DISTINCT filter_id) = 2;
DISTINCT. Might be >=2 depending on whether the actual problem is "must have only 4 & 48" or "must have both 4 & 48"This is a "sets-within-sets" query
You can try to get count base on the condition, then find the variant_id count equal 2
Schema (MySQL v5.6)
CREATE TABLE T(
variant_id INT,
filter_id INT
);
INSERT INTO T VALUES (1585593211,4);
INSERT INTO T VALUES (1585593211,48);
INSERT INTO T VALUES (1585593212,4);
INSERT INTO T VALUES (1585593212,49);
Query #1
SELECT variant_id
FROM T t1
where filter_id in (4,48)
group by variant_id
HAVING COUNT(*) = 2;
| variant_id |
| ---------- |
| 1585593211 |
or
select variant_id
from T
group by variant_id
having sum(filter_id = 4) > 0 and
sum(filter_id = 48) > 0
SELECT variant_id
FROM TABLENAME where filter_id in (4,48) group by variant_id having count(distinct filter_id)>1;
group by ,where filter_id would filter all the data which has 4 and 48
for a specific variant ID.
but the actual requirements would be met when we add
having count(distinct filter_id)>1;
this would make sure both 4 and 48 would be there in the filter_id.
count(distinct *) otherwise multiple 4s or 48s will result in a row.
