Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

Help with SQL query

hotel booking system asp.net sql vb.net

This topic has been archived. This means that you cannot reply to this topic.
2 replies to this topic

#1 steveyd18


    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 17 March 2015 - 11:07 AM

Hey guys,


This is the schema I have for my hotel booking system.

  • Customer Table (CustomerID, title, Firstname, Surname, address, town, county,postcode,country, tel, email, password, confirmpassword)

  • Room Table (RoomID, roomtype, description)

  • Reservation Table (ReservationID, CustomerID, CheckIn, CheckOut, NumberofOccupants, TotalPrice)

  • Room_booking (ReservationID, RoomID)

The scenario is the following:


The user will select an arrival date, and departure date from the calendar, and then selects from the drop down list the room type they want. Say for example they select a standard room, for the 18/03/2015 to the 21/03/2015, but that room has alreadly been booked by another user for those dates. How can I can detect that this room is booked using an sql query???


I would be greatful if yous wonderful programmers on this site could help me on this?

#2 WingedPanther73


    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 17 March 2015 - 01:14 PM

My guess is that to select ALL reservations with full info you would do the following SQL:


SELECT * FROM Reservation_Table INNER JOIN Room_Booking on (Reservation_Table.ReservationID=Room_booking.ReservationID) INNER JOIN Room_Table ON (Room_booking.RoomID=Room_Table.RoomID) INNER JOIN Customer_Table on (Reservation_Table.CustomerID=Customer_Table.CustomerID);


From there it's just a matter of changing the joins and adding a where clause to get the info you want.

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/

#3 BlackRabbit


    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts

Posted 19 March 2015 - 05:29 PM

Yes. Check the roomtypes where they don't exist on the reservation table for the given table.

Is the Room booking table really necessary in this case?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download