postgreSQL query - find next lesson

Question

I'm trying to write a PostgreSQL query to list the two instruments with the lowest monthly rental fee, also tell when the next lesson for each listed instrument is scheduled. I have these two tables:

//Table lesson
lesson_id | instrument_id | start
001       | 01            | 2021-01-01 10:00:00
002       | 01            | 2021-01-02 10:00:00
003       | 02            | 2021-01-04 10:00:00
004       | 02            | 2021-01-05 10:00:00

//Table instrument
instrument_id | fee_per_month
01            | 300
02            | 400
03            | 500

And I want:

instrument_id | fee_per_month | lesson_id | start
01            | 300           | 001       | 2021-01-01 10:00:00
02            | 400           | 003       | 2021-01-04 10:00:00

Getting the two instruments with lowest fee has been solved. How do I get the next lesson for these two instrument with lowest fee?

Answer 1

One option uses a lateral join:

select i.*, l.lesson_id, l.start
from instrument i
left join lateral (
    select l.*
    from lesson l
    where l.instrument_id = i.instrument_id and l.start >= current_date
    order by l.start
    limit 1
) l on true

This brings the first lesson today or after today's date for each instrument (if any).

You could also use distinct on:

select distinct on (i.instrument_id) i.*, l.lesson_id, l.start
from instrument i
left join lesson l on l.instrument_id = i.instrument_id and l.start >= current_date
order by i.instrument_id, l.start