Solved

MYsql Query using not like

Posted on 2012-04-05
7
347 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 500 total points
ID: 37814539
Try:

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

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 78

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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

737 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