MySQL - get the nearest date and show it for every row in table

Question

I have 2 tables - Equipments and Reservations.

Equipment table looks like this:

ID     NAME
1      termo
2      hydro
3      force
4      hammer

Reservation table:

ID     EQUIPMENT_ID     START         END
1      2                25.3.2015     26.3.2015
2      2                26.3.2015     27.3.2015
3      1                28.3.2015     29.3.2015
4      3                27.3.2015     28.3.2015

I want to be able to print all equipments and the nearest date in the future from the current date for each equipment.

So the output looks like this when the date is (24.3.2015):

NAME      START       END
termo     28.3.2015   29.3.2015
hydro     25.3.2015   26.3.2015
force     27.3.2015   28.3.2015
hammer    null        null

I tried some nested queries, not functioning. To find the nearest future date from the current date is that i know

SELECT start FROM reservations WHERE start > NOW() ORDER BY start LIMIT 1

but i dont know how to join the tables to find the nearest date for each equipment. Any ideas?

Answer 1

I think a correlated subquery is the easiest way to do this. However, you want three columns from the reservation table, so let's get the id and join in for the rest:

select e.*,
       (select r.id
        from reservations r
        where r.equipment_id = e.id and r.start > now()
        order by r.start asc
        limit 1
       ) as next_resid
from equipment e;

The join then requires a subquery:

select e.name, r.start, r.end
from (select e.*,
             (select r.id
              from reservations r
              where r.equipment_id = e.id and r.start > now()
              order by r.start asc
              limit 1
             ) as next_resid
      from equipment e
     ) er join
     reservations r
     on er.next_resid = r.id;

Note that end is not a good name for a column. Although it is not a MySQL reserved word, it is a SQL keyword.