Query Select from table

Question

I'm working on a project about hotel room reservations.

I have 4 tables :

Rooms ( room_id, category, price);

booking_rooms (idbr, room_id, booking_id);

bookings (booking_id,client_id, checkin, checkout);

clients(client_id, fistname,lastname, address,phone,email);

I want to create a search form to check available rooms between checkin and checkout date and category too.

SELECT * FROM rooms 
 WHERE room_id NOT IN (select room_id 
                         from bookings_rooms 
                        WHERE (checkin < '$checkindate' 
                               AND checkout > '$checkoutdate') 
                           or (checkin> '$checkindate' 
                               AND checkin< '$checkindate'))

This is what I did. And it's not working.

Can you guys help me with this. Can you give me an idea ?

starko starko · Accepted Answer · 2015-02-18T12:39:32

select * from rooms 
where room_id not in 
         (select room_id from bookings_rooms
                         where booking_id where not in //or in :)
                                        (select booking_id from bookings
                                                 where (checkin < '$checkindate' 
                                                AND checkout > '$checkoutdate') 
                                                or (checkin> '$checkindate' 
                                                AND checkin< '$checkindate')))

Query Select from table

1 Answers