HI i have this SQL query to get all avaible rooms.
SELECT DISTINCT rooms.room_ID, room_number, room_floor, room_seats FROM rooms JOIN reservations
WHERE rooms.room_ID NOT IN (select room_ID from reservations)
OR reservations.room_ID=rooms.room_ID AND reservation_check_in NOT BETWEEN CAST('2016-02-15' AS DATE) AND CAST('2016-02-28' AS DATE)
AND reservation_check_out NOT BETWEEN CAST('2016-02-15' AS DATE) AND CAST('2016-02-20' AS DATE)
ORDER BY room_number
This query is working but only when is minimum one reservation in database. If there is no reservation, all rooms are avaible, no matter what the date was given.
If(numberOfReservationsInDatabase == 0)
{
this query is not working(no rooms but should in this case show all rooms in database)
}
else if(numberOfReservationsInDatabase > 0)
{
this query is working(show only avaible rooms based on date)
}
How to change this query to work if(numberOfReservationsInDatabase == 0)?