MySQL get the nearest future date to given date, from the dates located in different table having Common ID
For this query, I suggest applying your WHERE
condition >= CURDATE()
and then SELECT
the MIN(dated)
with GROUP BY client_id
:
SELECT b.client_id, MIN(b.dated) FROM banquet b
WHERE b.dated >= CURDATE()
GROUP BY b.client_id;
From this, you can add the necessary JOIN
to the client table to get the client name:
SELECT b.client_id, c.name, MIN(b.dated) FROM banquet b
INNER JOIN client c
ON c.id = b.client_id
WHERE b.dated >= CURDATE()
GROUP BY b.client_id;
SQLFiddle: http://sqlfiddle.com/#!9/aded8/18
EDITED TO REFLECT NEW PARTS OF QUESTION:
Based on the new info you added - asking how to handle nulls and the 'meal' column, I've made some changes. This updated query handles possible null values (by adjusting the WHERE clause) in dated, and also includes meal information.
SELECT b.client_id, c.name,
MIN(b.dated) AS dated,
IFNULL(b.meal, '-') AS meal
FROM banquet b
INNER JOIN client c
ON c.id = b.client_id
WHERE b.dated >= CURDATE() OR b.dated IS NULL
GROUP BY b.client_id;
or you can take some of this and combine it with Gordon Linoff's answer, which sounds like it will perform better overall.
New SQLFiddle: http://sqlfiddle.com/#!9/a4055/2
One approach uses a correlated subquery:
select c.*,
(select max(dated)
from banquet b
where b.client_id = c.id and
b.dated >= CURDATE()
) as dated
from client c;
Then, I would recommend an index on banquet(client_id, dated)
.
The advantage of this approach is performance. It does not require an aggregation over the entire client table. In fact, the correlated subquery can take advantage of the index, so the query should have good performance.