Solved

MYsql Query using not like

Posted on 2012-04-05
7
356 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
Six Sigma Control Plans

615 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