mysql query - clash between not in and/or

Question

I'm trying to get all my products , from A specific categories and without some products from those categories .

i did this query:

SELECT `ps_product`.`id_product`,`ps_product_lang`.`name` 
FROM `ps_product`, `ps_product_lang` 
WHERE  
    `ps_product`.`id_product` = `ps_product_lang`.`id_product`
    AND `ps_product`.`id_manufacturer` NOT IN (3,7,10,11,13,14,19,22,23,24,25,30,31,32,33,34,37,38,41,42,43,44,45,46,47,48,49,50) 
    AND `ps_product_lang`.`name` not in ( '%OLP%' ,'%LicSAPk%' ,'%SPLA%','%SA OLP%')

The problem is that i'm still getting products with the string 'SPLA' or 'SA OLP' - because the NOT IN clash with the categories

How to solve it ?

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, May 29 '20 at 21:20

score 1 · Answer 1 · answered May 29 '20 at 21:20

The operator IN is used to match a value exactly and not partially against a list of values. So you can't use wildcards like '%'.
You have to use the operator LIKE for each of the values:

AND `ps_product_lang`.`name` not like '%OLP%' 
AND `ps_product_lang`.`name` not like '%LicSAPk%' 
AND `ps_product_lang`.`name` not like '%SPLA%'
AND `ps_product_lang`.`name` not like '%SA OLP%'

score 1 · Accepted Answer · answered May 29 '20 at 21:36

As a starter: always use standard join (with the on keyword) rather than old-school implicit joins (with commas in the from clause): this old syntax has fallen out of favor decades ago and should not be used in new code.

Then: you need multiple like conditions rather than in. If you have many values, regexp matching comes handy to shorten the syntax:

SELECT p.id_product,pl.name 
FROM ps_product p
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
WHERE  
    p.id_manufacturer not in (3, 7, 10, 11, 13, 14, 19, 22, 23, 24, 25, 30, 31, 32, 33, 34, 37, 38, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) 
    AND pl.name not regexp 'OLP|LicSAPk|SPLA|SA OLP'

Note that I used table aliases to shorten the syntax. I also remove the backticks around identifiers: most of the time they are not needed, and just make the query less easy to follow (you can add them back if this causes issues).

mysql query - clash between not in and/or

2 Answers2