MySQL query help (join, subselect)

Question

I have 2 tables: orders and bookings.

The orders table contain unique orders from customers. Each customer has booked a locker for a period of 1 to 5 years and therefore the bookings table can contain 1 to 5 rows pr. order (1 row for each year). Each row in the booking table contains and end_date which is the same date every year (20XX-06-30).

I want to select all the orders where the corresponding final end_date in the bookings table is this year (2014-06-30).

SELECT DISTINCT orders.id 
FROM   orders, 
   bookings 
WHERE  orders.id = bookings.order_id 
   AND bookings.end_date = '2014-06-30' 

The problem with this query is that it also selects the orders where the end_date in the booking rows continue the following years (2015-06-30, 2016-06-30 etc).

Answer 1

I am not sure I understood well, but here's a solution for what I understood, this should get you the order ids where there last end date (max) is 2014-06-30.

SELECT orders.id, MAX(bookings.end_date)
FROM orders INNER JOIN bookings 
ON orders.id = bookings.order_id 
GROUP BY bookings.order_id
HAVING MAX(bookings.end_date) = '2014-06-30'

Answer 2

Maybe join to the bookings again, checking for a larger booking date for the same order id:-

SELECT orders.id 
FROM orders
INNER JOIN bookings ON orders.id = bookings.order_id 
LEFT OUTER JOIN bookings2 ON orders.id = bookings2.order_id AND bookings2.end_date > bookings.end_date
WHERE bookings.end_date = '2014-06-30'
AND bookings2.end_date IS NULL