How do I query this table?plz - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I query this table?plz

Author

Topic

a4nsd
Starting Member

20 Posts

Posted - 2007-07-14 : 06:34:46

Hi every one
I have two table called. tbl_Room and tbl_Booking
tbl_Room (id,RoomNo,Price,NumOfAdult,NumOfChild)
tbl_Booking(id,checkin,checkout,roomid)
with roomid is the foreign key of tbl_Room.Checkin, checkout are the date people booking room
I have some example records
tbl_Booking
Checkin Checkout RoomID
08/01/2007 10/01/2007 1 (DD/MM/YYYY)
16/01/2007 22/01/2007 1
22/01/2007 29/01/2007 1
My problem is: When user input checkin and checkout date, how can i query from tbl_Booking to check if the room is available or not?
Example: if user input checkin =11/01/2007 and checkout =15/01/2007 .Room is availabe
If user input checkin=11/01/2007 checkout=17/01/2007.Room is not available

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-14 : 14:39:15

is this a homework problem? what do you have so far?

elsasoft.org

a4nsd
Starting Member

20 Posts

Posted - 2007-07-15 : 03:58:50

I have been thought about this problem for 1 week?I don't know how to do.Any one help?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-15 : 14:33:52

what have you come up with after thinking about it for a week? nothing?

elsasoft.org

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-07-15 : 17:17:06

I would add one tbl_roomspecification with elements as
1bed/2bed/suite
smoking/non-smoking
TV/WiFi/etc...

as well as tbl_reservations

normally hotels have more than just 1 room.

I would do some subquery nesting and calculate some additional fields as:

select roomID, (select min(daysofstay) from tbl_reservations where {condition}) as availabitily from ....

or anything with conditions:

EXISTS or
NOT EXISTS or
ANY

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-15 : 17:44:28

I think you better post some sample data and your expected output based on your provided sample data.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources