MySQL Case select query

Question

I have a users table with marital_status field with values from 0-5:
(0-single
1-married
2-divorced
3-widowed
4-in a relationship)

I am trying to run a select query on this table and output the corresponding values for the marital_status field instead of the number.

This is the query I am using:

   SELECT user_id,user_first_name, user_last_name,user_email_1,IF(user_gender="0","Male","Female")     AS user_gender,
user_birthdate,
CASE WHEN user_marital_status=0 THEN "Single" END AS user_marital_status, CASE WHEN    user_marital_status = 1 THEN "Married" END AS user_marital_status,
CASE WHEN user_marital_status=2 THEN "Divorced" END AS user_marital_status, CASE WHEN   user_marital_status = 3 THEN "Widowed" END AS user_marital_status,
CASE WHEN user_marital_status = 4 THEN "In a relationship" END  AS user_marital_status
FROM users a

However, when i run this query it created 5 different columns for user_marital_status How do I get the marital_status to be just one column with different values?

Here is very good resource on mysql if else, case statement. All are explained with examples. — RN Kushwaha
– RN Kushwaha, Commented Mar 8, 2015 at 15:23

Mureinik · Accepted Answer · 2014-12-12 15:11:41Z

1

You need a single case clause with several when clauses:

SELECT user_id,
       user_first_name, 
       user_last_name,
       user_email_1,
       IF(user_gender="0","Male","Female") AS user_gender,
       user_birthdate,
       CASE user_marital_status 
           WHEN 0 THEN 'Single'
           WHEN 1 THEN 'Married'
           WHEN 2 THEN 'Divorced'
           WHEN 3 THEN 'Widowed'
           WHEN 4 THEN 'In a relationship'
       END AS user_marital_status
FROM   users a

answered Dec 12, 2014 at 15:11

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Collectives™ on Stack Overflow

MySQL Case select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related