MySql select the nearest date in a jointure

Question

I'm using MySql and I need to do a specific query. I have 2 tables : Customer and Booking. Each customers can have many bookings. So I need to join theses tables but I want to display the nearest booking start date.

Here is my query :

SELECT b.number, b.start_date, c.*
FROM customer c LEFT JOIN booking b ON (c.id = b.id_customer)
GROUP BY c.id

So with my query I'll get all customers with random booking. What I need is to display the nearest (to the current date) booking start date with his number. Something like this :

SELECT nearest booking number, nearest start date, c.*
FROM customer c LEFT JOIN booking b ON (c.id = b.id_customer)
GROUP BY c.id

It is possible to do that ?

CREATE TABLE customer (
  id int,
  name varchar(80)
);

CREATE TABLE booking (
  id int,
  number varchar(80),
  customer_id int,
  start_date datetime
);

INSERT INTO booking (id, number, customer_id, start_date) VALUES
(1 , 'booking-1', 1, '2019-11-05 21:45:00'),
(2 , 'booking-2', 1, '2019-11-05 18:30:00'),
(3 , 'booking-3', 2, '2019-09-27 21:45:00'),
(4 , 'booking-4', 1, '2019-09-27 21:45:00'),
(5 , 'booking-5', 3, '2019-09-27 21:45:00');

INSERT INTO customer (id, name) VALUES
(1 , 'Paul'),
(2 , 'John'),
(3 , 'Morgan'),
(4 , 'Jane'),
(5 , 'Mike');

See : http://sqlfiddle.com/#!9/df0455/1

I tried using this:

SELECT b.number, b.start_date
FROM customer c LEFT JOIN booking b ON (c.id = b.customer_id)
INNER JOIN (
    SELECT id, MIN(ABS(DATEDIFF(NOW(), start_date)))
    FROM booking
    GROUP BY id
) nearest ON (b.id = nearest.id)

See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Nov 05 '19 at 18:12
See the linked question. Use `MIN(ABS(DATEDIFF(NOW(), start_date)))` as the criteria to find the nearest dates. — Barmar, Nov 05 '19 at 18:18
@Barmar I don't understand how the linked question can help me — John, Nov 05 '19 at 18:29
It's about finding the minimum or maximum row in each group of a query. You want to find the minimum date difference. — Barmar, Nov 05 '19 at 18:30
@Barmar I'm actually trying to do it here http://sqlfiddle.com/#!9/df0455/4 but I can't make it works — John, Nov 05 '19 at 18:38

Barmar · Accepted Answer · 2019-11-05T19:40:34.197

Your subquery doesn't select the row with the minimum date difference, it just calculates the date difference. And nearest.id is every ID in the table.

We need to use TIME_TO_SEC(TIMEDIFF()) rather than DATEDIFF() because some of the bookings are on the same day.

The subquery should be grouped by customer ID, not booking ID, and needs to give an alias to the time difference so we can use it in the outer query. The join needs to match the time difference to the booking table.

SELECT c.name, b.number, b.start_date
FROM customer c LEFT JOIN booking b ON (c.id = b.customer_id)
INNER JOIN (
    SELECT customer_id, MIN(ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date)))) as mindiff
    FROM booking
    GROUP BY customer_id
) nearest ON b.customer_id = nearest.customer_id AND ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date))) = mindiff

SQLFIDDLE

To get all the customers, put the join between booking and the subquery that finds the nearest booking by customer into a subquery, then left join that with customer.

SELECT c.name, x.number, x.start_date
FROM customer c 
LEFT JOIN 
(SELECT b.customer_id, b.number, b.start_date
 FROM booking b
 INNER JOIN (
    SELECT customer_id, MIN(ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date)))) as mindiff
    FROM booking
    GROUP BY customer_id
  ) nearest ON b.customer_id = nearest.customer_id AND ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date))) = mindiff
) AS x ON c.id = x.customer_id

SQLFIDDLE

Thank you, it is possible to display the customers even if they don't have associated booking ? In this example Jane and Mike can be displayed in the output with booking number and date with null value — John, Nov 05 '19 at 19:31

MySql select the nearest date in a jointure

1 Answers1