i need a rough idea how to construct some sql statement, hope some one can help.
i got 2 tables call booking and room, lets say booking got 3 attributes room-no, arrival-date and departure-date and table room have room-no. i need to search for available rooms (room.room-no) that are available base on the dates (departure and arrival date) that i will be giving to the sql statement. Ok this is the problem i facing now. Lets say i got 2 records in booking tat have:
room-no, arrival-date , departure-date
2 25-09-2003 29-09-2003
2 21-09-2003 24-09-2003
if i supplied the following sql:
select room.room-no from room left join booking on room.room-no = booking.roomno where (booking.arrival-date > "26-09-2003" and booking.arrival-date > "28-09-2003") or (booking.departure-date < "26-09-2003" and roombooking.dateto < "28-09-2003") or roombooking.roomid is null
the statment still give room-no 2 as valid result as the 2nd record match the statement. Any1 can help? I using MySql btw.
thanks in advance.