How to write MySQL Query IF ELSEIF ELSE

Question

I am trying to write MySQL query if else for this table,

table name: users

user_id | username | password | status
1  | john  | 1 | 1
2  | jessi | 2 | 0
3  | mark  | 1 | 2

status: 1 - enable, 0 - disable, 2 - deleted

I wrote SQL to get status data :

SELECT username, user_id, IF( STATUS =1,  'Enable',  'Disable' ) AS user_status
FROM `users`

how to write MySQL if elseif else statement for get all three status

@MichaelBerkowski could you please explain how to write case for this — Gayan
– Gayan, Commented Jun 5, 2014 at 16:31
See answers below, something like... CASE WHEN STATUS = 1 THEN 'Enable' WHEN STATUS = 0 THEN 'Disable' WHEN STATUS = 2 THEN 'Deleted' ELSE 'Unknown Status' END — Michael Berkowski
– Michael Berkowski, Commented Jun 5, 2014 at 16:33

Arion · Accepted Answer · 2014-06-05 16:36:41Z

3

You can use a CASE statment for that. Like this:

SELECT 
   username, 
   user_id, 
   (CASE STATUS
     WHEN 1 
     THEN 'Enable'
     WHEN 0 
     THEN 'Disable'
     WHEN 2
     THEN 'Deleted'
     ELSE 'Unknowed' 
   END) AS user_status 
FROM  
   `users`

Reference:

13.6.5.1 CASE Syntax

edited Jun 5, 2014 at 16:36

answered Jun 5, 2014 at 16:29

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Berkowski Over a year ago

The OP has 3 states 1 - enalbe, 0 - disalbe, 2 - deleted

Arion Over a year ago

@MichaelBerkowski : Update the answer. With the cases. Thanks for pointing that out

marekful · Accepted Answer · 2014-06-05 16:30:38Z

1

You can use nested IFs:

SELECT username, user_id, IF( `status`=1,  'Enable',  IF(`status`=2, 'Deleted', 'Disabled') ) AS user_status FROM  `users`

answered Jun 5, 2014 at 16:30

marekful

15.4k6 gold badges39 silver badges63 bronze badges

Collectives™ on Stack Overflow

How to write MySQL Query IF ELSEIF ELSE

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related