SELECT id WHERE

Question

I have the Table:

product_id | filter_id
        68 | 2
        68 | 4
        66 | 1
        69 | 4
        67 | 1
        67 | 5

I want to get

`product_id` WHERE `filter_id` = '2' AND `filter_id` = '4'

ie, I need to get only product_id = 68

SELECT `product_id` FROM `filter` WHERE `filter_id` IN (2,4) - unsuitable

How can i do this?

Answer 1

You can use a group by to select all product_ids that have both filter_id's 2 and 4

select product_id
from mytable
where filter_id in (2,4)
group by product_id
having count(*) = 2 

if (product_id, filter_id) is not unique, then use count(distinct filter_id) = 2 to ensure that the product has both filter_id's

Answer 2

You can do it this way:

SELECT `product_id` 
FROM `filter` 
WHERE `filter_id` IN (2,4)
GROUP BY `product_id`
HAVING COUNT(DISTINCT `filter_id`)=2

Result:

product_id
----------
68

Result in SQL Fiddle