mysql - Select available rooms in given date range -

I am building a hotel booking system and I was just stuck while trying to make a question which all Available room types receive the given category.

I have two tables rooms and reservations . The hotel has rooms tables in their number ( id ) and their type ( type ).

Reservations made by the table Reservations customers Reservation number ( id ), number of associated rooms ( room_id ) , And the date range ( to and to )

I have tried this question:

  available_ room_number Select as room_ID, at roomstesting, type at least with joints reservation. Rd = reservation.room_id WHERE Reservations Reservations.e_from & lt; = '' 2014-03-07 19:00:00 'and reservation. E_to & gt; = '2014-03-08 19:00:00')  

I was trying to get all available room types from March 7 to 8. I was expecting to get the modern room as a result of the query because there is no reservation to overlap with modern room id 4 date range and all 3 rooms are all Reservations have been done from 6 March to 9 March. But I'm not getting the result that I need. Below is the structure of my database (simplified)

rooms

  | ID | Type | ||||||||||||||||||||||||||| | 1 | Mas Ter Suite | | 2 | Master Suites | | 3 | Modern rooms | 4 | Modern rooms  

Reservation

  | ID | Room_id | From To |||||||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||| | 1 | 1 | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 | | 2 | 2 | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 | | 3 | 3 | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |  

Expected Result

  | Available_room_number | Type | ||||||||||||||||||||||||||||||||||||||||| | 4 | Modern rooms  

Anyone here can tell me how I would like to be correct with this view. Try it out:

  SELECT * From the room where there is no id (from reserve reservation where '2014- 03-07 19:00:00 'option & amp; e_to and' 2014-03-08 19:00:00 '& gt; e_frame)