changer changer - 1 month ago 11
SQL Question

Replace subquery with join

I have two tables

flats

id - primary key
title - text


flats_reservations

id - primary key
flat_id - foreign key to Flats(id)
creator_id - foreign key to users(id)
is_reserved - boolean
created_at - current_timestamp


If flats_reservations has these rows

id=1, flat_id=12, creator_id=5, is_reserved=1, created_at=2016-10-10 00:00:00
id=2, flat_id=12, creator_id=5, is_reserved=0, created_at=2016-10-10 00:00:05
id=3, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:10


Then only user with id=4 can remove reservation (e.g. insert following row (is_reserved=0)

id=4, flat_id=12, creator_id=4, is_reserved=0, created_at=2016-10-10 00:00:10


No other user can make this.

How can I select list of all flats with their current (e.g. latest) reservation status?

Simple, but not effective way - for every flat

select is_reserved from flat_reservations where flat_id={id} order by created_at desc limit 1


This can be done with subquery too

select flats.id, flats.title, (select is_reserved from flat_reservations where flat_id=flats.id order by created_at desc limit 1) as is_reserved from flats;


But how can I make this more effective?

Answer

This is essentially your query:

select f.id, f.title,
       (select is_reserved
        from flat_reservations fr
        where fr.flat_id = f.id
        order by fr.created_at desc
        limit 1
       ) as is_reserved
from flats f;

This is a fine way to do what you want. You want an index on flat_reservations(flat_id, created_at, is_reserved) if performance is an issue.

Comments