selecting multiple fields using in where clause

Question

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.

However, I want ALL queries to include the following statement

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

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.

If I add it to the where clause, it shows a blank. Yes, I know I do need to redo the coding and working on that, but in the meantime. — H.L.
– H.L., Commented Feb 9, 2019 at 21:18

LukStorms · Accepted Answer · 2019-02-10 11:43:51Z

2

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.

edited Feb 10, 2019 at 11:43

answered Feb 9, 2019 at 21:33

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

selecting multiple fields using in where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related