How to select the max aliased value in MySQL

Question

I'm pretty new to mysql and I have this SQL here:

SELECT * , MAX(meeting_date) AS recent_meeting_date
FROM driver
INNER JOIN  meeting_attendee ON meeting_attendee.attendee_email = driver.driver_email
INNER JOIN  meeting ON meeting.meeting_id = meeting_attendee.meeting_id
    WHERE recent_meeting_date < UTC_TIMESTAMP
    GROUP BY driver_id
    ORDER BY driver_id;

I have a driver table with drivers details and inner joins from meeting table where there's a list of all meetings and their and meeting_attendee table where there's a list of all drivers attended meetings in the past and future.

I need to have a sql that shows a list of all drivers who haven't booked a meeting yet, means that their recent meeting date is less than today's date and NO greater than today's date.

This sql doesn't run because we can't use recent_meeting_date in a WHERE clause. How can I fix the sql to make it work?

Please add a few rows of sample data and the expected result. — The Impaler, Jan 29 '21 at 18:53
Your query is malformed. It probably runs in MySQL 5.7.4 or older, but even then the result is unpredictable. — The Impaler, Jan 29 '21 at 18:54

score 0 · Accepted Answer · answered Jan 29 '21 at 19:05

You are getting an error because you cannot use alias references in the where clause. In you query you reference recent_meeting_date in the were clause. To fix your query you need to use the HAVING clause and you will be able to solve your problem. To more information about WHERE vs HAVING take a look on this stackoverflow

Here is the full query with the having clause:

    SELECT * , MAX(meeting_date) AS recent_meeting_date
    FROM driver
    INNER JOIN  meeting_attendee ON meeting_attendee.attendee_email = driver.driver_email
    INNER JOIN  meeting ON meeting.meeting_id = meeting_attendee.meeting_id
    GROUP BY driver_id
    HAVING recent_meeting_date < UTC_TIMESTAMP
    ORDER BY driver_id;

How to select the max aliased value in MySQL

1 Answers1