CODEWITHSUNDEEP
mysqlsqlnull
Maverick
Maverick

Reputation: 1187

MySQL : How to get records are have non null values?

I want to get all records in case the result_1 is not null like below :

SELECT ID,
       Code, 
       NULLIF(CompareWithField,2.25) as result_1 
  FROM `data`
 WHERE Indexed = 0 
   and code = 142 
   and 'result_1' is not null

But instead, every time I run the query, I receive a result even if result_1 reports NULL.

Any solution ?

Upvotes: 1

Views: 255

Answers (3)

Maverick
Maverick

Reputation: 1187

I found the answer. The problem was the alias names is not allowed in the WHERE clause.

So, I changed the query to this and it worked.

SELECT ID,
       Code, 
       CompareWithField 
  FROM `data`
 WHERE Indexed = 0 
   and code = 142 
   and NULLIF(CompareWithField,2.26) is not null

Upvotes: 0

Brian Hooper
Brian Hooper

Reputation: 22084

It's because you have result_1 inside quotes. That turns it from a column name into a text value, which isn't null. Try...

SELECT ID,Code, NULLIF(CompareWithField,2.25) as result_1 FROM `data`
    WHERE Indexed=0 and code=142 and result_1 is not null

Upvotes: 3

Jason McCreary
Jason McCreary

Reputation: 73031

By your query, result_1 is an aliased field. As such, it is possible that CompareWithField is still NULL or 2.25 and therefore resulting in a NULL.

Upvotes: 1

Related Questions