Good morning guys, I have a problem with a query that has been very complicated, you see, I have three tables, rooms, reservations and rooms_reservas, the first shows all the information related to the rooms of a hotel, the second is related to reservations and the third is a breakage table since, in this case, a reservation may have several rooms.
I have tried to make a query that only brings me the rooms available on a particular date, the query is as follows:
SELECT DISTINCT hab.* FROM habitacion hab
LEFT OUTER JOIN habitacion_reserva habr ON habr.id_habitacion = hab.id
LEFT OUTER JOIN reserva res ON res.id = habr.id_reserva
WHERE hab.tipo = 1
AND (((( '2018-06-10' not between res.fecha_ingreso and res.fecha_salida) AND ( '2018-06-17' not between res.fecha_ingreso and res.fecha_salida))
OR (res.fecha_ingreso is null OR res.fecha_salida is null)) OR ((( '2018-06-09' between res.fecha_ingreso and res.fecha_salida) OR ( '2018-06-17' between res.fecha_ingreso and res.fecha_salida)) AND res.estado = 4))
My logic in doing this was the following, first exclude those rooms that had reservations between the particular date as long as they were not canceled (status = 4 = Canceled), at first I worked but as more and more reservations began I started to bring all the rooms, I would like to know if there is a way in which if at least on one occasion the conditional is not met, then I removed the room from the results.