[sqlite] Efficient relational SELECT

[Jake Thaw]

Hi Simon,

I do this type of query all the time to avoid sub queries and aggregation.
This might be what you are looking for to satisfy the elegance criteria:

SELECT r.room_id,
       b.date
  FROM room    r
  LEFT JOIN
       booking b  ON r.room_id = b.room_id
  LEFT JOIN
       booking b2 ON r.room_id = b2.room_id
                 AND b2.date > b.date
 WHERE b2.booking_id IS NULL

I leave it to you to understand why this works.

Regards

Jake