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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…