MYSQL Select MAX Date in a 3 table join grouped by the ID in the 1st table

Question

I have three tables, one called BookingDetails that stores the client info. 140,000 records

CustomerRef	Name	Expiry
1	John	2023-12-17
2	Sarah	2024-06-17
3	Fred	2024-09-10
4	Bill	2022-03-11

The second called BookingRequest that contains the requests that client has made. 83,000 records

ID	CustomerRef
1	1
2	1
3	2
4	3
5	3
83000	4

and the third called RequestLog that stores the request status of each Booking Request. 110,000 records

ID	RequestID	DateAdded	Status
1	1	2023-08-17	Refused
2	1	2023-08-18	Pending
3	2	2023-08-19	Booked
4	3	2023-09-14	Pending
5	4	2023-09-19	Pending
6	5	2023-10-17	Refused
110000	83000	2023-10-18	Pending

I need to find all current clients that haven't booked, so I am looking to perform a join to return only the MAX(Status) grouped by Client where the expiry date is greater than now. The problem I have is that a client can have multiple booking requests. The query I have done works correctly if a client only has one request, but if a client has a request where the the lastest Status is 'booked' but then has another request where the latest status is 'pending' it is appearing in the list. I need to ONLY show clients where the latest status of ANY of their requests is anything apart from 'booked'.

SELECT BookingDetails.CustomerRef, BookingDetails.Name, BookingDetails.Expiry, b.Status AS Holidaystatus FROM BookingDetails JOIN BookingRequest ON BookingRequest.CustomerRef= BookingDetails.CustomerRef INNER JOIN RequestLog a ON a.RequestID=BookingRequest.ID JOIN (SELECT MAX(DateAdded) maxdate, RequestID FROM RequestLog GROUP BY RequestID) AS b ON a.RequestID=b.RequestID AND a.DateAdded=b.maxdate WHERE a.Status != 'Booked' AND DATE(BookingDetails.Expiry) > CURDATE() GROUP BY  BookingDetails.CustomerRef

What I want to see is

CustomerRef	Name	Expiry	Status
2	Sarah	2024-06-17	Pending
3	Fred	2024-09-10	Refused

What I am seeing is

CustomerRef	Name	Expiry	Status
1	John	2023-12-17	Pending
2	Sarah	2024-06-17	Pending
3	Fred	2024-09-10	Refused

Answer 1

You can use a subquery to identify the latest status for each client's requests and then filter based on that result.

SELECT
    BD.CustomerRef,
    BD.Name,
    BD.Expiry,
    RL.Status AS HolidayStatus
FROM
    BookingDetails BD
LEFT JOIN (
    SELECT
        BR.CustomerRef,
        MAX(RL.DateAdded) AS MaxDate
    FROM
        BookingRequest BR
    INNER JOIN RequestLog RL ON BR.ID = RL.RequestID
    GROUP BY
        BR.CustomerRef
) LatestStatus ON BD.CustomerRef = LatestStatus.CustomerRef
LEFT JOIN BookingRequest BR ON BD.CustomerRef = BR.CustomerRef
LEFT JOIN RequestLog RL ON BR.ID = RL.RequestID
WHERE
    LatestStatus.MaxDate IS NULL OR LatestStatus.MaxDate = RL.DateAdded
    AND DATE(BD.Expiry) > CURDATE()
    AND (RL.Status IS NULL OR RL.Status != 'Booked');

This query first creates a subquery (LatestStatus) to find the maximum date added for each customer's requests. Then, it joins the BookingDetails table with the LatestStatus subquery and the other necessary tables.