CODEWITHSUNDEEP
phpmysqlnullwhere-clause
Manigandan Arjunan
Manigandan Arjunan

Reputation: 2265

How to check null values in multiple fields in where clause mysql

I am trying to match null values in multiple fields in table using where clause, for example 

 SELECT * FROM manage_users WHERE coalesce(surname, firstname, lastname, physical_address, postal_address, telephone_number, fax_number, cell_number, email,medical_aid_fund, medical_aid_number, allergies, emergency_contatct_person, emergency_contatct_number, id_number,gender, age_on_raceday, age_groups, shirt_size, type_of_club, roag_membership_number, csa_licence_number, provinical_running_licence_number, provinical_running_canoeing_number, tsa_licence_number, licence_number, number_of_comrade_marathon_completed, number_of_two_oceans_completed,    number_of_duzi_completed, number_of_nonstop_duzi_completd,number_of_amashova_completed, 
number_of_argus_completed, number_of_947_completed, number_of_midmar_mile_completed, make_of_running_shoes, make_of_road_bike, make_of_mtb, make_of_wetsuit) is NOT NULL and id='16'

I have used coalesce for this, is it a right way. I am not getting the expected output using this. Can anyone help me getting the result. It would be greatly appreciated.

Upvotes: 1

Views: 5162

Answers (2)

Saranya Sadhasivam
Saranya Sadhasivam

Reputation: 1294

Query which will not accept null and empty values. It will select the row in which the mentioned where columns have value in it.

Select * from manage_users where (surname and firstname and lastname and physical_address and postal_address) > ''

Upvotes: 1

Barmar
Barmar

Reputation: 781068

COALESCE returns the first non-null value in the argument list. So your query will succeed if any of the fields are not null, it doesn't require them all to be non-null.

You can instead use:

SELECT * FROM manage_users WHERE CONCAT(<list of columns>) is NOT NULL and id='16'

CONCAT(), like most ordinary functions, returns NULL if any of the arguments are NULL.

To check for any empty values, add:

AND LEAST(<list of string columns>) != ''

This should just be the string columns; if you mix strings and numbers in LEAST, it converts all the strings to numbers, and any string that doesn't begin with a number will be converted to 0, so it won't do a proper test.

Upvotes: 4

Related Questions