MYSQL If at least one result does not match, remove it from

Question

I need to do a very complex search

Context: I have 3 tables: rooms, reservation, and room_reservation. The first one have all info of the rooms in a hotel, the second have info of all reservation and the third is because a reservation have multiple rooms.

I need know what rooms are available in a expecific date, I really close, I need that if at least one data does not match the condition of the search then the related room is not shown.

This is my query:

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))

In theory the query works but when I have many reservation, the query simply return all rooms.

When you do `LEFT JOIN`, the conditions on the second table need to be in the `ON` clause, not `WHERE`. Otherwise, the non-matching rows will be filtered out. See https://stackoverflow.com/questions/47449631/return-default-result-for-in-value-regardless/47449788#47449788 — Barmar, Jun 05 '18 at 20:24
If you want to get rid of the reservated rooms then, in my opinion, focus on the not between. On the last line you are searching for is null and for is between? This is strange because of the not between. — Michael, Jun 05 '18 at 20:33
Because i need get what rooms are avaiable, so I think I should rule out rooms that have reservations on that date, I need that if at least one data does not match the condition then not return that room, but having so many reservations, I will still return it. — Daniela Romero Zapata, Jun 05 '18 at 21:06

Michael · Accepted Answer · 2018-06-06T04:52:58.230

1

Try something like this (simplified)

Select * FROM rooms 
    LEFT JOIN room_reservation ON (rooms.id = rooms_reservation.id) 
    LEFT JOIN reservations ON (rooms_reservation.id = reservation.id) 
        reservation 
    WHERE 1=1 
        ...
        AND whateverfield = 1 
        ....
        AND ('2018-06-09' NOT BETWEEN reservation.start AND reservation.end)
        AND ('2018-06-17' NOT BETWEEN reservation.start AND reservation.end)

    GROUP BY rooms

edited Jun 06 '18 at 04:52

answered Jun 05 '18 at 20:40

Michael

556
2
8

But I need get what rooms are available – Daniela Romero Zapata Jun 05 '18 at 20:58
Yes sure, you area selecting all exept the reservated rooms – Michael Jun 06 '18 at 04:32
This is only simplified. You can expand it with your join. – Michael Jun 06 '18 at 04:34

MYSQL If at least one result does not match, remove it from

1 Answers1