How to get all avaible rooms?

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Abdu Rafeeq10y ago
Just use "union" statement for each condition.

ex:

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

SELECT DISTINCT rooms.room_ID, room_number, room_floor, room_seats FROM rooms JOIN reservations
WHERE 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)

union

SELECT DISTINCT rooms.room_ID, room_number, room_floor, room_seats FROM rooms JOIN reservations
WHERE reservation_check_out NOT BETWEEN CAST('2016-02-15' AS DATE) AND CAST('2016-02-20' AS DATE)
ORDER BY room_number