[SOLVED] How can i use IFNULL in Where clause

How can i use IFNULL in Where clause

I want to use IFNULL() in such a way that I can select the record containing NULL or, if a value is present, then select the record matchinga particular value.

My query is:

SELECT (@v:=2) AS Row, CL.LeaveTypeId, CL.NumberOfLeave
FROM `CompanyLeave` CL
WHERE(IFNULL(CL.EmploymentType,0)=3);

column EmploymentType can contain either an Integer or NULL.

I want to select the record matching the specified value, or, if none matches, then the record containing NULL.

Solution

I am interpreting the question as a prioritization. If a record with 3 exists, choose that. Otherwise, choose the one that is NULL, if it exists.

If so, this might do what you want:

SELECT (@v:=2) AS Row, CL.LeaveTypeId, CL.NumberOfLeave
FROM `CompanyLeave` CL
WHERE CL.EmployementType = 3 or CL.EmployementType IS NULL
ORDER BY (CL.EmployementType = 3) DESC
LIMIT 1;

This will return the row with 3, if present. Otherwise, it will return a row with NULL, if one exists.