How to use select Case in following SQL query
I have two tables users
and userdetail
. I am trying to create a view where if the status of a column userdetail
is 1 it should show Active
in the view and locked if the 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 someone please help me with this?
+3
source to share
2 answers
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 that in your request.
- The correct syntax for is
case
not using=
followed by clausesthen
(well, unless you want it to return a boolean). - Table aliases make the query easier to write and read.
-
New
is a bad name to represent. Although not reserved, it is a keyword.
+1
source to share
You cannot assign a value .. just select
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 'Blocked'
WHEN userdetail.status= '0' THEN 'Active'
END as my_eval_status,
users.phoneNumber AS number
FROM users
JOIN userdetail ON users.id = userdetail.reference
0
source to share