asked on

Help with Search Query - Using And/or

I am having a hard time getting the right results, its still including the word minifig in the return and I am trying to filter that out:

select distinct item_title, sold_price
from products
where product_type = 1
AND (item_title like '%2114%')
AND (item_title not like '%mini%figure%' or item_title not like '%minifig%')
AND (item_title LIKE '%chopov%')
ORDER BY sold_price desc

This is the result I am getting back. The word minifig is clearly still in there. How can i do this query to work better?
----------------------------------------------------------

new lego ninjago chopov spinner 2114
lego ninjago ''chopov'' 2114 nib new
lego 2114 ninjago chopov battle pack new
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago-chopov 2114 w/ black spinner brand new
new lego 2114 figure chopov in hand ninjago rare 2011
new lego ninjago chopov (2114)
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago-chopov 2114 w/ black spinner brand new
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago skeleton warrior chopov minifig 2259 2114
lego ninjago chopov 2114

ASKER CERTIFIED SOLUTION

michael_madsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

theideabulb

ASKER

Yes that worked much better, why does it need the AND and not work with the OR in the parenthesis?

michael_madsen

Basically what you have in that line is two conditions:

1) Item-title should not contain 'mini%figure'
2) Item-title should not contain 'minifig'

with "or" you are saying that either one can evaluate to true for the whole line to evaluate to true.

So Item-title like: "Lego ninjago skeleton warrior chopov minifig 2259 2114" evaluates like:

1) true
2) false

problem is that with "or" your whole line is still true.