CODEWITHSUNDEEP
mysqlsqlselectcase
db1
db1

Reputation: 3179

MySQL Case select query

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?

Upvotes: 1

Views: 213

Answers (1)

Mureinik
Mureinik

Reputation: 311338

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

Upvotes: 1

Related Questions