postgreSQL - find next lesson for specific instruments

I'm trying to write a PostgreSQL query to list the two instruments that are available for rent, 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_type | start  001       | 01            | 2021-02-01   002       | 01            | 2021-02-02  003       | 02            | 2021-02-04  004       | 02            | 2021-02-05  005       | 03            | 2021-02-06    //Table instrument  instrument_id | fee_per_month | availability  01            | 300           | yes  02            | 400           | no  03            | 500           | yes  

And I want:

instrument_type | fee_per_month | lesson_id | start  01            | 300           | 001       | 2021-02-01  03            | 500           | 005       | 2021-02-06  

SQL is new to me, and I have tried my best but didn't succeed:

SELECT  instrument.type AS "instrument",  instrument.fee_per_month AS "fee/month",  lesson.start AS "next lesson"    FROM instrument, lesson    LEFT JOIN LATERAL (      SELECT lesson.*      FROM lesson      WHERE lesson.start >= current_timestamp AND lesson.instrument_type = instrument.type      ORDER BY lesson.start      limit 1  ) lesson on true  GROUP BY "instrument", "rent/month", "next lesson"  ORDER BY "rent/month"  limit 2;  

How should I do it correctly?

https://stackoverflow.com/questions/65622289/postgresql-find-next-lesson-for-specific-instruments January 08, 2021 at 09:36AM