Vyshnavi Samudrala Vyshnavi Samudrala - 1 year ago 37
SQL Question

My query is not returning empty or null values from the table

This is my query and it is not returning empty or null column values in a particular row

SELECT if(b2b_image1 is not null or b2b_image1='' ,"1", "0") + if(b2b_image2 is not null or b2b_image2='', "1", "0") + if(b2b_image3 is not null or b2b_image3='', "1", "0") + if(b2b_image4 is not null or b2b_image4='', "1", "0") as non_empty FROM b2b_checkin_report WHERE b2b_booking_id='105';

Here, the 4th column b2b_image4 is empty, but it is returning me 4 as output


Answer Source

I didn't fully understand what you are trying to do. You want an indication of each column if its a null or an empty string? If so , you have a few errors :

SELECT if(b2b_image1 is null or b2b_image1='' ,'1', '0')
      ,if(b2b_image2 is null or b2b_image2='', '1', '0') 
      ,if(b2b_image3 is null or b2b_image3='', '1', '0') 
      ,if(b2b_image4 is null or b2b_image4='', '1', '0') 
FROM b2b_checkin_report
WHERE b2b_booking_id='105';

Or, you want to see the count of the columns that are null or empty, in that case your problem was that you used IS NOT NULL instead of IS NULL , which does exactly the opposite of what you intended :

SELECT if(b2b_image1 is null or b2b_image1='' ,1, 0)
        + if(b2b_image2 is null or b2b_image2='', 1, 0) 
        + if(b2b_image3 is null or b2b_image3='', 1, 0) 
        + if(b2b_image4 is null or b2b_image4='', 1, 0) as empty_cnt
FROM b2b_checkin_report
WHERE b2b_booking_id='105';

Also, for string use a single quote sign ' and not double " , although you don't even need strings here, you are summing up numbers, so use numbers!.