Conditional in MYSQL where clause

case, conditional-statements, mysql, where-clause

Solution

You can do this with `AND` and `OR` as long as you use sufficient parentheses.

Also I'm assuming that `activities.OrderBy` can be null. If that's not the case you can remove the null check:

SELECT activities.*, 
       activitytypes.orderby 
FROM   activities 
       LEFT OUTER JOIN activitytypes 
                    ON activities.typeid = activitytypes.typeid 
WHERE  activities.userid = 86 
       AND activities.typeid NOT IN ( 5, 10, 11, 12, 19 ) 
       AND ( ( activities.orderby = 1 
               AND activities.starttime >= '2013-08-26 04:00:00' 
               AND activities.endtime <= '2013-08-27 04:00:00' ) 
              OR ( ( activities.orderby IS NULL 
                      OR activities.orderby != 1 ) 
                   AND activities.activitydate = '2013-08-26' ) ) 
ORDER  BY activitytypes.orderby, 
          activities.starttime

Alternatively, if you still want to use `CASE`, you just need to close your `CASE` statement using `END`, like this:

SELECT          activities.*, 
                activitytypes.orderby 
FROM            activities 
LEFT OUTER JOIN activitytypes 
ON              activities.typeid = activitytypes.typeid 
WHERE           activities.userid = 86 
AND             activities.typeid NOT IN ( 5, 10, 11, 12, 19 ) 
AND             ( 
                  CASE 
                  WHEN activities.orderby = 1 THEN
                    activities.starttime >= '2013-08-26 04:00:00' AND activities.endtime <= '2013-08-27 04:00:00'
                  ELSE
                    activities.activitydate = '2013-08-26' 
                  END
                ) 
ORDER BY        activitytypes.orderby, 
                activities.starttime

Problem

I have a query that if a flag called OrderBy is 1 then it is a calendar event and I need to check for a range between two date time fields. Whereas all other types we just check for the day you are viewing. I research if statements and saw many posts where it was suggested a case be used so I tried to implement it into my query. My query needs the condition in the where. I know I have syntax issues and that is why I am here in the hopes that someone could point out the correct way to do this. Thank you for your time My query currently ``` SELECT activities.*, activitytypes.orderby FROM activities LEFT OUTER JOIN activitytypes ON activities.typeid = activitytypes.typeid WHERE activities.userid = 86 AND activities.typeid NOT IN ( 5, 10, 11, 12, 19 ) AND CASE WHEN activities.orderby = 1 THEN activities.starttime >= '2013-08-26 04:00:00' AND activities.endtime <= '2013-08-27 04:00:00' ELSE activities.activitydate = '2013-08-26' order BY activitytypes.orderby, activities.starttime ```

Original source