SQLize | PHPize | SQLtest

create table hotels ( id serial primary key, name text -- other fields ); create table rooms ( id serial primary key, hotel_id int references hotels(id), description text -- other fields ); create table bookings ( id serial primary key, room_id int references rooms(id), check_in date, check_out date, status smallint -- other fields ); -- get free rooms select * from rooms join hotels on hotels.id = rooms.hotel_id where not exists ( select true from bookings where room.id = bookings.room_id and 'requested_check_in' between check_in and check_out and 'requested_check_out' between check_in and check_out ) and ( -- other filters )