changer changer - 9 months ago 71
SQL Question

Replace subquery with join

I have two tables


id - primary key
title - text


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.title, (select is_reserved from flat_reservations where order by created_at desc limit 1) as is_reserved from flats;

But how can I make this more effective?

Answer Source

This is essentially your query:

select, f.title,
       (select is_reserved
        from flat_reservations fr
        where fr.flat_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.