How can I use MAXDATE in MySQL where clause

I am working with a scheduling database for events.

I am selecting from series of days and INNER JOINING them into the schedules parent "event". In my selection I want to make a condition where the selection has to be older than the CURDATE. Here is what I have tried but am getting an "improper use of grouping error"

SELECT MIN(event_schedule.event_day) as mindate, MAX(event_schedule.event_day) as maxdate, event.event_title  
FROM event_schedule 
INNER JOIN event ON event_schedule.event_id = event.id 
WHERE MAX(event_schedule.event_day) < CURDATE()
GROUP BY event.id

Solution

If you change your query to be the following, you should be fine:

SELECT MIN(event_schedule.event_day) as mindate, MAX(event_schedule.event_day) as maxdate, event.event_title  
FROM event_schedule 
INNER JOIN event ON event_schedule.event_id = event.id
GROUP BY event.event_title
HAVING maxdate < CURRENT_DATE()

I removed your WHERE clause in favor of a HAVING clause and added a GROUP BY clause. The GROUP BY is necessary for the query to run correctly, and the reason you are getting the error is because you have an aggregate function in your WHERE clause. The HAVING is like WHERE in that it is a filter (they are very different) but if you want to filter on an aggregated value, such as MAX, you need to do that in the HAVING.