[c00perzz]

Hi,

I have the following situation (poor programming post below so be kind!) :

Im building an availability calendar and i want to be able to search which rooms are availalble on a specific date.

TABLE: ROOMS
roomid
roomname

TABLE: BOOKINGS
bookingid
roomid
from
to

ROOM RECORDS
1 | 'BASEBALL'
2 | 'MOZES'
3 | 'CATER'

EXAMPLE #1:
BOOKING RECORDS
1 | 1 | 2015-02-05 | 2015-05-08
2 | 2 | 2015-02-12 | 2015-05-18
3 | 3 | 2015-02-05 | 2015-02-22

The above works fine. The problem is if a room has more than 1 bookings on different dates:

BOOKING RECORDS
1 | 1 | 2015-02-05 | 2015-05-08
2 | 2 | 2015-02-12 | 2015-05-18
3 | 3 | 2015-02-05 | 2015-02-22
4 | 3 | 2015-02-25 | 2015-02-27

On the above example, if i search for rooms between the dates 2015-02-05 to 2015-02-22 it will not show ROW #3 but it will show #4! If a room has booking within those days it does not show it (which is correct). If though that room has booking on another day which does not fall within the SEARCH RANGE it is shown as available!

I basically need help on the logic of this. If a room is booked on the searched dates then it must not show any other rows of it from other dates!

I hope i didnt confuse you 😊

[szuszi2]

What is your query? basicly you would want to do this: Spoiler (Click to View)SELECT * FROM bookings LEFT JOIN rooms on bookings.roomid=rooms.roomid WHERE from>=2015-02-05 and to<= 2015-02-22