MySQL SELECT query WHERE JOINed child rows contain all whitelisted column values per parent row
02:47 27 Dec 2015

I have the following SQL query, to search for products in this case:

SELECT
    sql_cache distinct p.products_id
FROM
    products p 
INNER JOIN
    filter_association_products fap 
        ON p.products_id = fap.products_id 
WHERE
    p.products_status = '1' 
    AND date_sub(curdate(),interval 3000 day) <= p.products_date_added 
    AND find_in_set(fap.filter_id,'130,128') 
ORDER BY p.products_date_added DESC

Currently the result I get is all the products that have fap.filter_id 128 and fap.filter_id 130, but I want it to list only products that have both of these filters.

Basically I want to use find_in_set to do this:

AND (fap.filter_id = '130' AND fap.filter_id = '128')

right now the way it functions is:

AND (fap.filter_id = '130' OR fap.filter_id = '128')

How can this be achieved?

mysql join aggregate-functions parent-child having