ASP date availability checking?

Question

I am making a system to book rooms but i am stuck with the availability check

I have a table with the cottage details in it(tblCottage)

and then one just for availability checks (tblAvail)

some code below

select * from tblCottage , tblAvail where tblCottage.intCottageid = tblAvail.intCottageid AND tblAvail.datbook = DATE '2007-11-18'

this brings back all bookings with that date but I dont know how to convert this in to checking all other combinations....

ie I know that room 204 is booked for that day so I want to list all the other rooms as available so the user can book them.

thanks for any help in advanced

BTW I am using Oracle

and yes the date is stored in the tblAvail Table

one again thanks

The SQL code:

SELECT C.intCottageid FROM tblCottage C INNER JOIN tblAvail A ON C.intCottageid = A.intCottageID WHERE A.datbook != '2007-11-18'

will bring back all the other bookings in the database but how will that help me find out what rooms can be booked for that period?

i think i need to somehow get the data from the first query which shows which rooms are not available into another query to remove them from the choices. so if room 204 is listed in the first query then it is removed from the list of available rooms as a product of the last query.

Answer 1

I'm not sure exactly how your tables are set up, but I'm guessing that when a room is booked, it gets an entry in tblAvail that indicates it is booked for a given day.

If that's the case, we just need the antithesis of your current query. I'm assuming you are using SQL Server or MySQL after version 4.1:

SELECT C.intCottageid FROM tblCottage C INNER JOIN tblAvail A ON C.intCottageid = A.intCottageID WHERE A.datbook != '2007-11-18'

UPDATE:

The code above should return all the cottages that do not have bookings on a given date. If it does not, it's because your database is not set up the way I expect it is to be set up. And short of seeing your data, I can't help you any more.