Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with Search Query - Using And/or

Posted on 2011-03-19
3
Medium Priority
?
268 Views
Last Modified: 2012-05-11
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
Comment
Question by:theideabulb
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
michael_madsen earned 2000 total points
ID: 35172542
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
 

Author Comment

by:theideabulb
ID: 35172548
Yes that worked much better, why does it need the AND and not work with the OR in the parenthesis?
0
 
LVL 2

Expert Comment

by:michael_madsen
ID: 35172579
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question