Search code examples
mysqlwhere-clausewhere-in

selecting multiple fields using in where clause

I have a MySQL query and this is used to bring up certain events. The event_type and etype will change depending on what someone chooses in the form. Below is what is added to the query based on a form on the webpage.

and event_type = '54' and etype = 'SP'

the full query is

select tevent.event_name, tevent.event_type, min(e_dates.event_date) as eventdate,  
       tevent.status, tevent.etype 
from (tevent LEFT JOIN event_dates on tevent.eventid=event_dates.eventid) 
Where status <> 'delete' 
AND YEAR(e_dates.event_date) >= YEAR( CURDATE( ) ) and event_type = '54' and etype = 'SP')  
group by tevent.eventid 
order by (case when tevent.status = 'closed' and e_dates.event_date >= curdate() then 0 else 1 end), 
         (case when find_in_set(`status`, 'open,pending,approved') then 0 else 1 end), 
         e_dates.event_date asc, tevent.eventid ASC

This works perfectly for what I need. I shows all the events that are a certain event types and event category.

enter image description here

However, I want ALL queries to include the following statement

((event_type = '54' and etype = 'SM') or (event_type = '50' and event_prelim = '2'))

enter image description here

the above statement will add the seminars to all event calendars, but will also show each particular event type based on what the person chooses.

Solution

  • I suppose your WHERE clause could look like this 

    WHERE status <> 'delete' 
  AND YEAR(e_dates.event_date) >= YEAR(CURDATE()) 
  AND (
         event_type NOT IN ('50','54')
      OR event_type IS NULL
      OR (event_type = '54' AND etype IN ('SP','SM')) 
      OR (event_type = '50' AND event_prelim = '2')
  )

    AND's are evaluated before the OR's.
    So when using both AND's and OR's in the criteria, putting the parentheses does matter.