MySQL gets the closest future date to the specified date, starting from dates located in another table with a common identifier

Question

I have two tables - Client and Banquet

Client Table ---------------------------- ID NAME 1 John 2 Jigar 3 Jiten ---------------------------- Banquet Table ---------------------------- ID CLIENT_ID DATED 1 1 2016.2.3 2 2 2016.2.5 3 2 2016.2.8 4 3 2016.2.6 5 1 2016.2.9 6 2 2016.2.5 7 2 2016.2.8 8 3 2016.2.6 9 1 2016.2.7 ---------------------------- :::::::::: **Required Result** ---------------------------- ID NAME DATED 2 Jigar 2016.2.5 3 Jiten 2016.2.6 1 John 2016.2.7 

The result to be generated is such that

1. Date, which is FUTURE: CLOSEST or EQUAL for the current date, which is further associated with the relevant client, must be filtered and ordered in the format specified in the Required result

CURDATE () for the current case - 5.2.2016

FAILED: query logic 1

 SELECT c.id, c.name, b.dated FROM client AS c, banquet AS b WHERE c.id = b.client_id AND b.dated >= CURDATE() ORDER BY (b.dated - CURDATE()); ------------------------------------------- OUTPUT ID NAME DATED 2 Jigar 2016.2.5 2 Jigar 2016.2.5 3 Jiten 2016.2.6 3 Jiten 2016.2.6 1 John 2016.2.7 2 Jigar 2016.2.8 2 Jigar 2016.2.8 1 John 2016.2.9 

FAILED: Query Logic 2

 SELECT c.id, c.name, b.dated FROM client AS c, banquet AS b WHERE b.dated = ( SELECT MIN(b.dated) FROM banquet as b WHERE b.client_id = c.id AND b.dated >= CURDATE() ) ORDER BY (b.dated - CURDATE()); ------------------------------------------- OUTPUT ID NAME DATED 2 Jigar 2016.2.5 2 Jigar 2016.2.5 3 Jiten 2016.2.6 3 Jiten 2016.2.6 1 John 2016.2.7 

sqlfiddle

UPDATE A further result to be generated is that

2. Customers WITHOUT: DATED should also be listed: may be with NULL

3. Information other than DATED in the BANQUET table should also be indicated

UPDATED Required Result

 ID NAME DATED MEAL 2 Jigar 2016.2.5 lunch 3 Jiten 2016.2.6 breakfast 1 John 2016.2.7 dinner 4 Junior - - 5 Master - supper 

Answer 1

For this query, I suggest applying the WHERE >= CURDATE() condition, and then SELECT MIN(dated) using 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 required JOIN table 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

CHANGE NEW PART OF QUESTION:

Based on the new information you added, asking how to handle zeros and the food column, I made some changes. This updated query processes possible null values ​​(by changing the WHERE clause) in a dated format, and also includes food 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 work best.

New SQLFiddle: http://sqlfiddle.com/#!9/a4055/2

Answer 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 the banquet(client_id, dated) index banquet(client_id, dated) .

The advantage of this approach is performance. It does not require aggregation across the entire client table. In fact, a correlated subquery can take advantage of the index, so the query should have good performance.

Answer 3

try it

 SELECT banquet.client_id, MIN(banquet.dated) FROM banquet WHERE banquet.dated >= CURDATE() GROUP BY banquet.client_id; 

if you want to limit the output, for example, your expected result is 3 entries, so you can use limit in this case

 SELECT banquet.client_id, MIN(banquet.dated) FROM banquet WHERE banquet.dated >= CURDATE() GROUP BY banquet.client_id limit 3; 

if you want to use the join with the Client table, you can use `join '

 SELECT banquet.client_id, MIN(banquet.dated) FROM banquet JOIN Client ON Client.id = banquet.client_id WHERE banquet.dated >= CURDATE() GROUP BY banquet.client_id; 

also use limit with the last request.

Answer 4

Date that is CLOSEST or EQUAL at the current date

To be attentive to your requirements, i. e. "CLOSEST" can be both before and after the current date, the request must use the (absolute) difference between it and the DATED value.

Thus, this can be achieved as follows:

 SELECT c.id, c.name, b.dated, MIN(ABS(TIMESTAMPDIFF(DAY, b.dated, CURDATE()))) AS `delta` FROM banquet AS b INNER JOIN client AS c ON b.client_id = c.id GROUP BY b.client_id 

Here is the SQL script