• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

MYsql Query using not like

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
ralphs1961
Asked:
ralphs1961
  • 3
  • 2
  • 2
2 Solutions
 
johanntagleCommented:
Try:

and (Status not like 'Credit%' or Status is null)
0
 
arnoldCommented:
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
 
johanntagleCommented:
@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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ralphs1961Author Commented:
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
 
arnoldCommented:
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
 
johanntagleCommented:
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
 
ralphs1961Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now