How to implement MySQL CASE with OR condition?

MySQLMySQLi Database

Here is the syntax of MySQL CASE OR condition

SELECT yourColumnName1,.....N ,
   CASE WHEN yourColumnName2=0 or yourColumnName2IS NULL THEN 'yourMessage1'
ELSE 'yourMessage2'
END AS yourAliasName
FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table ReservationSystems
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > Name varchar(20),
   - > isSeatConfirmed tinyint
   - > );
Query OK, 0 rows affected (0.61 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Larry',0);
Query OK, 1 row affected (0.12 sec)
mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Bob',1);
Query OK, 1 row affected (0.15 sec)
mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Mike',NULL);
Query OK, 1 row affected (0.14 sec)
mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('David',1);
Query OK, 1 row affected (0.17 sec)
mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('Carol',0);
Query OK, 1 row affected (0.19 sec)
mysql> insert into ReservationSystems(Name,isSeatConfirmed) values('James',1);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from ReservationSystems;

The following is the output

+----+-------+-----------------+
| Id | Name  | isSeatConfirmed |
+----+-------+-----------------+
|  1 | Larry |               0 |
|  2 | Bob   |               1 |
|  3 | Mike  |            NULL |
|  4 | David |               1 |
|  5 | Carol |               0 |
|  6 | James |               1 |
+----+-------+-----------------+
6 rows in set (0.00 sec)

Here is the MySQL CASE with OR condition

mysql> select Name,
   - > CASE WHEN isSeatConfirmed=0 or isSeatConfirmed IS NULL THEN 'YOUR SEAT IS NOT CONFIRMED'
   - > ELSE 'YOUR SEAT IS CONFIRMED'
   - > END AS SEATCONFIRMSTATUS
   - > FROM ReservationSystems;

The following is the output

+-------+----------------------------+
| Name  | SEATCONFIRMSTATUS          |
+-------+----------------------------+
| Larry | YOUR SEAT IS NOT CONFIRMED |
| Bob   | YOUR SEAT IS CONFIRMED     |
| Mike  | YOUR SEAT IS NOT CONFIRMED |
| David | YOUR SEAT IS CONFIRMED     |
| Carol | YOUR SEAT IS NOT CONFIRMED |
| James | YOUR SEAT IS CONFIRMED     |
+-------+----------------------------+
6 rows in set (0.00 sec)
raja
Published on 19-Mar-2019 15:05:24
Advertisements