• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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
0
theideabulb
Asked:
theideabulb
  • 2
1 Solution
 
michael_madsenCommented:
select distinct  item_title, sold_price
from products
where product_type = 1
AND (item_title like '%2114%')
AND (item_title not like '%mini%figure%')
AND (item_title not like '%minifig%')
AND (item_title LIKE '%chopov%')      
ORDER BY sold_price desc
0
 
theideabulbAuthor Commented:
Yes that worked much better, why does it need the AND and not work with the OR in the parenthesis?
0
 
michael_madsenCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now