Get all tables where there is no booking on this time or date

Question

So basically I have a tables table. And a bookings table. A table can be assigned to a booking via the table_no column. The booking also has a reservation_time and reservation_date columns. What I'd like my query to do, is to return all tables that aren't linked to a booking on a certain time or date. It's really bugging me.
Here is what my query looks like as of now
select t.id, t.number
FROM tables t JOIN 
     bookings b
     ON b.table_no = t.number JOIN
     reservation_time_data r
     ON r.id = b.reservation_time
WHERE t.number != b.table_no AND b.reservation_date != '2020-07-22' AND 45 NOT BETWEEN r.start_time AND r.end_time

Gordon Linoff · Answer

You seem to want not exists.  Based on your sample query, I think this is:
select t.id, t.number 
from tables t 
where not exists (select 1
                  from bookings b join
                       reservation_time_data r 
                       on r.id = b.reservation_time 
                  where b.table_no = t.number and
                        b.reservation_date = '2020-07-22' and
                        45 >= r.start_time and
                        45 <= r.end_time
                 );

Mousa Khodaei · Answer

I think you can get it with left join like this
    select t.id, t.number FROM tables t Left JOIN bookings b ON b.table_no = t.number 
 WHERE  b.table_no is null AND (b.reservation_date = '2020-07-22' Or b.[your time column here] BETWEEN b.start_time AND b.end_time )

Get all tables where there is no booking on this time or date

2 Answers

Add your own answers!

Ask a Question