Solved

Help with Search Query - Using And/or

Posted on 2011-03-19
3
257 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 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now