Using Case in WHERE condition MYSQL-CodePudding

Is it possible to use CASE statement in WHERE condition like below?

SELECT act.id_activity FROM activity act
LEFT JOIN work w ON w.id_work = act.id_work
WHERE 
w.work_type=1
AND w.work_tender in (1,2)
AND act.id_activity_type IN 
(CASE WHEN w.work_tender=1 THEN '2,3' WHEN w.work_tender=2 THEN '2,3,4,9' END)

it returns no error but the results always display act.id_activity_type = 2 instead of 2,3 or 2,3,4,9

In this case 1 work (table work) can have many activities (table activity). i want to display activities based on work.work_tender type. if work.work_tender=1 then need to choose activity.id_activity_type IN (2,3). if work.work_tender=2 then need to choose activity.id_activity_type IN (2,3,4,9)

CodePudding user response：

You can try to write correct logic by OR & AND.

SELECT act.id_activity FROM activity act
LEFT JOIN work w ON w.id_work = act.id_work
WHERE 
w.work_type=1
AND (
    (act.id_activity_type IN ('2','3') AND w.work_tender=1) OR 
    (act.id_activity_type IN ('2','3','4','9') AND w.work_tender=2)
)

CodePudding user response：

I think case is used for that case. I think it is possible to use in that case. And also for orderby phrase.

CodePudding user response：

Maybe you can try with this:

SELECT * 
FROM activity a 
JOIN work w
ON w.work_tender=
   CASE WHEN a.id_activity_type IN (2,3) THEN 1
        WHEN a.id_activity_type IN (2,3,4,9) THEN 2 END;

I'm using CASE expression here to assign value matching w.work_tender if the a.id_activity_type fit the condition. So, if a.id_activity_type IN (2,3) THEN 1 will match with w.work_tender=1 similarly with a.id_activity_type IN (2,3,4,9) THEN 2 will match with w.work_tender=2.

Demo fiddle