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

Question

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 ?

Answer 1

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.

Answer 2

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

Answer 3

Hi,

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