Francamente è un kran kasino e continuo a non capire da dove saltino fuori le select.
Mi sa che stai facendo una sorta di copia-incolla di materiale lurkato a destra e sinistra, e non mi è chiaro a cosa ti serva LEFT JOIN in quanto potresti avere dati NULL

Inizierei per tanto ad abbandonare il LEFT JOIN (se ho capito bene quello che vuoi fare), costruirei tutti i join
(non so, li metto così)
e poi schiafferei in fondo tutte le condizioni del mondo
codice:
select * from hotel,room,booking_check_availability,hotel_room_price,hotel_seasonal_price,hotel_promotion
where 
(hotel.idhotel=room.idroom)
and
(room.idroom=booking_check_availability.idroom)
and 
(room.id_room=hotel_room_price.id_room)
and
(hotel_seasonal_price.id_room=room.id_room)
and
(hotel.id_room=room.id_room)
AND
... qui ci metti tutte le condizioni che vuoi