Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MYsql Query using not like

Posted on 2012-04-05
7
Medium Priority
?
362 Views
Last Modified: 2012-04-11
I have a query and it doesn't work very well

I have put in a not like statement in the query and my thinking is that the query would catch everything including the null values for the field except it doesn't.  If the field is empty it doesn't catch the record.

The query is

SELECT (DATE_FORMAT(Posted, '%Y%m01')) as YM, Count(INum) AS ICount, AVG(Sub) AS Average, Sum(Sub) AS SubTotal, Store, Emp FROM main WHERE (Cdate between 20110331 AND 20120331) AND ANumber like '%cash%' and Status not like 'Credit%' group by Store, Emp, YM
0
Comment
Question by:ralphs1961
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 37814539
Try:

and (Status not like 'Credit%' or Status is null)
0
 
LVL 79

Expert Comment

by:arnold
ID: 37814622
Do you get an error?
To the suggestion johanntagle made, I'd suggest the change in the order, first check whether it is null thus preventing the not like check on nulls.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37814707
@arnold - good point.

Btw, @ralphs1961, see http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_not-like for an explanation why you specifically need to test for nulls.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:ralphs1961
ID: 37824483
johanntagle answer seems to work I am just going to check it and make sure that it works.  It sure seems and gives me what I am looking for.  My question is why does it work?  I have made queries before that haven't worked because of this as well.
0
 
LVL 79

Expert Comment

by:arnold
ID: 37824919
not like '<pattern>%' likely matches something you did not think it will match.
reverse the condition and see the data that is being returned.
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 2000 total points
ID: 37826675
My question is why does it work?  I have made queries before that haven't worked because of this as well.

The link I gave you above gives you the explanation.  Basically using NOT LIKE on a NULL will always return NULL.  What you need is something that will evaluate to TRUE in order for the row in question to be part of the result set.  So checking if the value of Status for that row is null does the trick.
0
 

Author Closing Comment

by:ralphs1961
ID: 37833999
Hey guys, I double checked the query and made my own calculations on the actual rows that were returned as well with and without the clause.  AWESOME thanks hugely for your  help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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 …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Six Sigma Control Plans
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…

670 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