CreativeMind CreativeMind - 4 years ago 176
SQL Question

MySQL find_in_set for results that have all the search strings in the set

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?

Answer Source

to solve this you need to tell the sql engine how many matching results you want per type. generally you dont specify the comma delimited elements, but rather use find_in_set for looking for a specific thing in an unknown list of elements. you could easily change that to an IN() statement.

WHERE p.produccts_status = 1
AND ....
AND fap.filder_id IN(130, 128)

its a better standard to write the code with the functions / methods provided that do what they are intended to do. that being said you could keep it as is if you wanted.

you need to add a GROUP BY p.products_id and include a HAVING clause to count the number of records

HAVING count(p.products_id) = 2

I put 2 here because you are only looking for 2 items that have to match you can easily change this to be however many you are looking for. since its something you are specifically looking for you should be able to specify how many matching rows you want (because you are specifying them in the IN statement)

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        fap.filter_id IN (130, 128) 
GROUP BY   p.products_id
HAVING     COUNT(*) = 2
ORDER BY   p.products_date_added DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download