Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    6

    Unanswered: Check hotel room availability

    Hi, I am working on this hotel booking website
    I have the following tables:

    booking (bookingid,arrival,departure,roomid,customerid)PK bookingid

    customers (customerid,firstname,lastname,address)PK customerid

    rooms (roomid,roomnumber,roomtypeid) PK roomid

    roomtypes (roomtypeid,roomtype,numbeds,priceper night)PK roomtypeid
    roomtypeid has 1 for single, 2 for double, 3 for twinbed, 4 for triple

    I hope this is right.What I need is to check room availability on a certain date to/from.
    Since there are no bookings at the moment all rooms should be available.Also is not clear to me how to select
    a roomtype on a given date.Thanks a lot.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you want all rooms which are not booked for the proposed period. a proposed period could be a day or days

    Code:
    select
      rooms.roomid,
      roomtypes.description  #proposed new column in  room types that describes a room
    from
      rooms,
      roomtypes
    left join roomtypes on #join the room to the room type so you know what types rooms are available
      rooms.roomtype = roomtypes.roomtype  #should be a match  assumign yu have defiend PK/FK's correctly and enforced RI
    left join bookings on #join to the bookings table so you know what rooms are booked
      rooms.roomid = booking.roomid
    where 
      booking.arrival <= proposedbookingend #proposedbookingend is the enquiry's proposed end date
      and booking.departure >= proposedbookingstart #proposedbookingstart  is the enquiry's proposed start date
      and booking.roomid in NULL
    #filter out any bookings which have room numbers as this indicates that there is a booking for the period

    wthat shoudl get you sort of close to wher eyou want to be

    but the next query woudl be to display rooms that are partially booked during that period so you can see if you can shuffle a booking around (say you have no room for the proposed stay, but if you push an existing booking to a new room that might free up a room for the porposed stay
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2014
    Posts
    6

    Check hotel booking room availability

    Quote Originally Posted by healdem View Post
    so you want all rooms which are not booked for the proposed period. a proposed period could be a day or days

    Code:
    select
      rooms.roomid,
      roomtypes.description  #proposed new column in  room types that describes a room
    from
      rooms,
      roomtypes
    left join roomtypes on #join the room to the room type so you know what types rooms are available
      rooms.roomtype = roomtypes.roomtype  #should be a match  assumign yu have defiend PK/FK's correctly and enforced RI
    left join bookings on #join to the bookings table so you know what rooms are booked
      rooms.roomid = booking.roomid
    where 
      booking.arrival <= proposedbookingend #proposedbookingend is the enquiry's proposed end date
      and booking.departure >= proposedbookingstart #proposedbookingstart  is the enquiry's proposed start date
      and booking.roomid in NULL
    #filter out any bookings which have room numbers as this indicates that there is a booking for the period

    wthat shoudl get you sort of close to wher eyou want to be

    but the next query woudl be to display rooms that are partially booked during that period so you can see if you can shuffle a booking around (say you have no room for the proposed stay, but if you push an existing booking to a new room that might free up a room for the porposed stay

    Hi I ran your code and got this error: Not unique table/alias: 'roomtypes'- I have no idea what it means and how to correct this error. Also I don't understand too well foreign keys and unique keys, I know primary keys are unique, foreign keys I thought were same entries in other tables like in my case I have customerID in table customers and customerID in table bookings (this is a foreign key?)- I appreciate if you will kindly give me some explanation and tell me what to do, thanks.

  4. #4
    Join Date
    Oct 2014
    Posts
    6

    Check hotel booking room availability

    Quote Originally Posted by healdem View Post
    so you want all rooms which are not booked for the proposed period. a proposed period could be a day or days

    Code:
    select
      rooms.roomid,
      roomtypes.description  #proposed new column in  room types that describes a room
    from
      rooms,
      roomtypes
    left join roomtypes on #join the room to the room type so you know what types rooms are available
      rooms.roomtype = roomtypes.roomtype  #should be a match  assumign yu have defiend PK/FK's correctly and enforced RI
    left join bookings on #join to the bookings table so you know what rooms are booked
      rooms.roomid = booking.roomid
    where 
      booking.arrival <= proposedbookingend #proposedbookingend is the enquiry's proposed end date
      and booking.departure >= proposedbookingstart #proposedbookingstart  is the enquiry's proposed start date
      and booking.roomid in NULL
    #filter out any bookings which have room numbers as this indicates that there is a booking for the period

    wthat shoudl get you sort of close to wher eyou want to be

    but the next query woudl be to display rooms that are partially booked during that period so you can see if you can shuffle a booking around (say you have no room for the proposed stay, but if you push an existing booking to a new room that might free up a room for the porposed stay

    Hi I ran your code and got this error: Not unique table/alias 'roomtypes' what does it mean? And how should I correct it? Looking at my tables which ones should be unique? I understand PK are unique. Also have no idea how to apply an alias to a table.I appreciate your help thank you.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its probably my commenting that is causing the issue
    remove all the lines starting with #
    then check you have a table called roomtypes
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •