Hotel Double Booking problem

[jason chan]

Hello

ive got a problem with an sql statement to ensure that a particular room is not double booked.

I have two tables

Bookings
Book_ref
Arriving_date
departure_date


and

Rooms
Room_Num
Room_Type
Rate

What im trying to do is to do a search by entering the arriving date and the departure date and also room_type so that it will display all the rooms that are not currently been taken.

I researched an example like this: -

select * from room, bookings inner join on room.room_num = bookings.room where room.room_num not in bookings.room order by room_num.

but it doesnt really take into consideration the arriving date or departure date. or room type.

Any one have any ideas. please contact me and i;ll explain the situation abit more throughly if confused. msn, jason_chan1984@hotmail.com

thanks