user2719164 user2719164 - 5 months ago 12
SQL Question

Conditional in MYSQL where clause

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

Answer

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
Comments