匹配单行

There is a list of Filter Id assigned to Products. Multiple Filter Can be assigned to Same Product. So i am looking for the query to fetch product id if according to passed filter id

eg I have

$filter_id = Array
(
    [0] => 2
    [1] => 12
    ...
 )

In this case it should return product id 40 I need all filter id matched to get particular product so i used AND

I tried SELECT product_id FROM tablename WHERE filter_id = 2 AND filter_id = 12

Thanks

Here is how u can do to find the product which has both 2,12 as filter_id.

select product_id
from products
where filter_id in (2,12) 
group by product_id
having count(distinct filter_id) = 2 

You need to change

having count(distinct filter_id) = 2

to the number of filter_id you are checking.

Just using filter_id in (2,12) to your query will return all the product which has 2 or 12.

DEMO

Your query

SELECT product_id FROM tablename WHERE filter_id = 2 AND filter_id = 12

will return 0 rows as there can't be a row having filterid equal to 2 and 12 at sametime , it is impossible in relational database design

Instead use IN operator to specify multiple required values for a column .

SELECT product_id FROM tablename WHERE filter_id IN(2,12)