CODEWITHSUNDEEP
mysql
jchan
jchan

Reputation: 47

MYSQL return row

I've a table with records in mysql as below and I would like to get the result, if no value in receivedfmuser and topm then no record (means 0 row) will be displayed, but mysql doesn't work as mysql still count it as 1 row?

select receivedfmuser, topm from tb_postatus where pono = 36 and receivedfmuser is not null and topm is not null


TABLE
|tid|pono|receivedfmuser|topm      |
|1  |36  |02/02/2015    |          |
|2  |27  |02/03/2015    |02/03/2015|

Upvotes: 0

Views: 58

Answers (3)

Venkata Krishna
Venkata Krishna

Reputation: 1776

In your above example, the value in topm in row1 is actually an empty string value and not NULL. That is why it satisfies not null condition and is displaying the record. So, use the following

select receivedfmuser, topm from tb_postatus 
where pono = 36 and 
receivedfmuser != '' and 
topm != ''

This wont display the rows having NULL and also those having an empty string value.

Upvotes: 1

user2360915
user2360915

Reputation: 1139

If in above example the value you are checking for null is topm, then the issue is your query because your query is validating "receivedfmuser" as not null value.

select t.receivedfmuser, t.topm, t.pono
from test t
where `receivedfmuser` IS NOT NULL;

receivedfmuser, topm, pono

'02/02/2015', NULL, '36' '02/02/2015', '02/03/2015', '27'

Then changing the query to "topm"

select t.receivedfmuser, t.topm, t.pono
from test t
where `topm` IS NOT NULL;

receivedfmuser, topm, pono

'02/02/2015', '02/03/2015', '27'

The result show 1 row the one with topm not null.

With your example : 

select t.receivedfmuser, t.topm, t.pono
from test t
where `topm` IS NOT NULL and t.pono = 36;

receivedfmuser, topm, pono

Not result is returned.

Hope this helps.

And yes, if the value you are checking to be null is actually an empty value, your query will not work as mysql will not understand null as an empty string.

Upvotes: 0

Harsh Chunara
Harsh Chunara

Reputation: 585

Try below query

select receivedfmuser, topm 
    from tb_postatus 
    where pono = 36 
    and receivedfmuser is not null
    and receivedfmuser != ""
    and topm is not null
    and topm != ""

Upvotes: 0

Related Questions