Solved

MYsql Query using not like

Posted on 2012-04-05
7
351 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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

734 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