MySQL Booking system: Getting available rooms -

i have problem query locating available rooms in simple hotel booking system.

my table structure looks following:

hotels
hotelroomtypes (intersects hotel, room , roomtype)
bookings
orders

there more it, use of other tables sums in query, looks following:

    select distinct          hotels.hotelname         ,hotels.hotelid         ,hotels.address         ,hotels.description         ,images.url         ,roomtypes.price         ,roomtypes.roomtypename         ,roomtypes.roomtypeid         ,count(distinct hotelroomtypes.hrid) availablerooms      hotelroomtypes     inner join hotels on (         hotelroomtypes.hotelid = hotels.hotelid         ,         hotels.countryid = 1 // e.g. united states     )     inner join roomtypes on (         hotelroomtypes.roomtypeid = roomtypes.roomtypeid         ,         roomtypes.roomtypeid = 1 // e.g. suite     )     right outer join bookings on (         hotelroomtypes.hrid not in (             select hrid bookings             bookings.from between '2015-06-13' , '2015-06-16'             or bookings.to between '2015-06-13' , '2015-06-16'         )     )     inner join images on (         hotels.imageid = images.imageid     )     group          hotels.hotelname     having count(hotelroomtypes.hrid) > 0     order hotels.hotelname asc, availablerooms asc 

now problems lay getting correct number of booked rooms, , hiding hotel query if rooms taken.

i used having inner join on bookings, when had empty bookings table, query failed 100% , didn't show hotels.

with left joins, had false positives @ times, using these types of joins return every room hotel has or none @ all.

inner naturally returns no hotels if there no bookings present, expected. using earlier.

how can correcly structure query give me exact amount of available rooms on each hotel, returning hotels have available rooms, if there no bookings search through?

edit table structures:

 hotels:  _________________________________________________________________ | hotelid | hotelname | countryid | description | otherfieldshere | |_________|___________|___________|_____________|_________________| |    1    |  example  |     1     |   |       | |_________|___________|___________|_____________|_________________|  hotelroomtypes:  ______________________________________ | hrid | hotelid | roomtypeid | roomid | |______|_________|____________|________| |  1   |    1    |     1      |   1    | |______|_________|____________|________|  bookings:  _______________________________________________ | bookingid |       |         | orderid | |___________|____________|____________|_________| |    1      | 2015-05-13 | 2015-05-16 |    1    | |___________|____________|____________|_________|  orders:   ________________________________________ | orderid | reference |      email       | |_________|___________|__________________| |    1    |   12345   | some@example.com | |_________|___________|__________________|  rooms:   _____________________ | roomid | roomnumber | |________|____________| |   1    |    500     | |________|____________| 

unfortunately seems alcohol , problem solving isn't option when you've been working 7 hours straight without break.

i restructured query following:

    select distinct          hotels.hotelname         ,hotels.hotelid         ,hotels.address         ,hotels.description         ,images.url         ,roomtypes.price         ,roomtypes.roomtypename         ,roomtypes.roomtypeid         ,count(distinct hotelroomtypes.hrid) availablerooms      hotels     inner join hotelroomtypes on (         hotelroomtypes.hotelid = hotels.hotelid         ,         hotels.countryid = ?         ,         hotelroomtypes.hrid not in (             select hrid bookings             bookings.from between ? , ?             or bookings.to between ? , ?         )     )     inner join roomtypes on (         hotelroomtypes.roomtypeid = roomtypes.roomtypeid         ,         roomtypes.roomtypeid = ?     )     inner join images on (         hotels.imageid = images.imageid     )     group          hotels.hotelname     having count(hotelroomtypes.hrid) > 0     order hotels.hotelname asc, availablerooms asc 

which solved problem.