CODEWITHSUNDEEP
phpmysqlsql
user1187
user1187

Reputation: 2198

Mysql Case with Or condition

How to write a case in mysql query which checks for null or 0 for a particular column


CREATE TABLE tblConfirmationStatus (Confirm_Status TINY INT)

INSERT INTO tblConfirmationStatus 
Confirm_Status
VALUES
(1),
(0),
(1),
({null}),
(0),
(1),
({null})

Required Output

ConfirmStatus

   Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed

0 or Null - Not Confirmed, 1-Confirmed 

SELECT CASE Confirm_Status 
            WHEN NULL OR 0 THEN 'Not Confirmed' 
             ELSE  'Confirmed' END AS ConfirmStatus
  FROM tblConfirmationStatus;

Upvotes: 20

Views: 34862

Answers (3)

Omesh
Omesh

Reputation: 29091

SELECT IF((Confirm_Status IS NULL OR Confirm_Status = 0), 
           'Not Confirmed', 'Confirmed') AS ConfirmStatus
FROM tblConfirmationStatus;

Upvotes: 5

OMG Ponies
OMG Ponies

Reputation: 332581

There's two options for CASE statements - the one you posted, or:

SELECT CASE 
        WHEN Confirm_Status IS NULL OR Confirm_Status = 0 THEN 'Not Confirmed' 
        ELSE  'Confirmed' 
       END AS ConfirmStatus

But you could probably use:

SELECT CASE 
        WHEN Confirm_Status > 0 THEN 'Confirmed' 
        ELSE  'Not Confirmed' 
       END AS ConfirmStatus

NULL is the absence of a value, so checking for values above zero should fall into the same category as zero.

Upvotes: 36

Adriaan Stander
Adriaan Stander

Reputation: 166396

Have you had a look at using IFNULL operator.

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

Upvotes: 1

Related Questions