SQL Scheduling - Select All Rooms Available for Given Date Range?

You want all the rooms which do not have a booking in that date range, i.e. , If your sql engine does subqueries.

You want all the rooms which do not have a booking in that date range, i.e. , If your sql engine does subqueries... Select * From Rooms r where not exists (Select * From Bookings Where room = r. Room And startBooking @startRange) HIK, to understand the need for the room = r.

Room clause try these two queries Query One (with room = r. Room clause) Select r. *, Case Where Exists (Select * From Bookings Where room = r.

Room And startBooking @startRange) Then 'Y' Else 'N' End HasBooking From Rooms r Query Two(without room = r. Room clause) Select r. *, Case Where Exists (Select * From Bookings Where startBooking @startRange) Then 'Y' Else 'N' End HasBooking From Rooms r Notice the first one returns different values in HasBooking for each row of the output, because the subquery is 'Correleated' with the outer query... it is run over and over agaio, once for each outer query results row.

The second one is the same value for all rows... It is only executed once, because nothing in it is dependant on which row of the outer query it is being generated for.

This looks like what I'm looking for. I'll give it a whirl. – HK1 May 24 at 13:49 Do I really need this where condition: room = r.

Room? I'm trying to figure out what it does. – HK1 May 24 at 13:51 Yes, else it will consider bookings for all rooms, which would cause all rooms to appear booked even if only one is – Rob Cowie May 24 at 13:53 Possibly too much info, but if you need to handle open-ended bookings, replace endBooking > @startRange with (endBooking > @startRange OR endBooking IS NULL) – Rob Cowie May 24 at 13:55 Charles, Rob, I guess I'm still baffled by room=r.room.

The problem is that in MS Access my subquery is unaware of the table/entity r since r is in the parent/main query. – HK1 May 24 at 14:03.

I believe something like this could work to be entirely honest im not a 100% sure SELECT r. RoomID, b. BeginDate, b.

EndDate FROM tblRoom as r INNER JOIN tblBooking be ON r. RoomID = b. AssignedRoomID WHERE r.

RoomDate BETWEEN b. BeginDate AND b.EndDate.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.