How to use select Case in the following SQL query

Question

I have two tables users and userdetail. I am trying to create a view in which if status of userdetail column is 1 it should show Active in view and blocked if status is 0:

CREATE VIEW `new` AS 
SELECT 
users.id AS id,
userdetail.healthissues AS healthissues,
users.fullName AS fullname,
userdetail.`status`,

 CASE status
 WHEN  userdetail.status ='1' THEN  userdetail.`status`= 'Blocked'
        WHEN  userdetail.status= '0' THEN  userdetail.`status` ='Active'
    END ,

users.phoneNumber AS number
FROM users
JOIN userdetail ON users.id = userdetail.reference

This code does not give the desired result. Can some please help me out with this?

Answer 1

I think this is the correct syntax for what you want:

CREATE VIEW `new` AS 
    SELECT u.id AS id, ud.healthissues AS healthissues, u.fullName, 
           ud.status as orig_status,
           (CASE WHEN ud.status = 1 THEN 'Blocked'
                 WHEN ud.status = 0 THEN 'Active'
            END) as status,
           u.phoneNumber AS number
    FROM users u JOIN
         userdetail ud
         ON u.id = ud.reference;

Notes:

• I'm not sure if you want to select the status as well as the string, but you have it in your query.
• The correct syntax for case does not use = then the then clauses (well, unless you want it to return a boolean).
• Table aliases make the query easier to write and to read.
• New is a bad name for a view. Although not reserved, it is a keyword.