The case when then, but with the condition And inside, when and before

Question

In the following query, I want to add an AND condition inside CASE WHEN and before THEN is this possible?

e.g. WHEN 'r' AND table1.name = "jones" THEN 'very high'

SELECT table1.id, table1.name, CASE table1.event WHEN 'r' THEN 'very high' WHEN 't' THEN 'very low' ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value ORDER BY table2.value DESC LIMIT 1) END AS risk FROM table1 ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC 

Answer 1

You can rewrite your expression to do what you want

 SELECT table1.id, table1.name, CASE WHEN table1.event = 'r' AND table1.name = 'jones' THEN 'very high' WHEN table1.event = 't' AND table1.name = 'smith' THEN 'very low' ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value ORDER BY table2.value DESC LIMIT 1) END AS risk FROM table1 ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC 

note that you need to remove table1.event after the CASE statement. here

Answer 2

Anything that evaluates to boolean (true or false) can be in the WHEN state of the CASE statement. Therefore, you can replace 'r' with:

('r' AND table1.name='jones')

Thinking more about this, you may need to lose table1.event after CASE

 SELECT table1.id, table1.name, CASE WHEN (table1.event = 'r' AND table1.name='Jones') THEN 'very high' WHEN table1.event = 't' THEN 'very low' ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value ORDER BY table2.value DESC LIMIT 1) END AS risk FROM table1 ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC 

Answer 3

Switch from case <column> when <value> then ... to case when <condition> then ... :

 CASE WHEN table1.event = 'r' AND table1.active = 1 THEN 'very high' ...