Hostel Reservations
-
I am developing developing a hostel booking system, and I am trying to come up with a query that allows me to find all the rooms that are availiable on a particular day. I have tried using the except statement but it has not work, and I have also tried using a temporary table, but that has not worked. I have also attempted to select all the rooms that are availiable on a particular day and after that I have conducted a left join on the rooms tables, so as to try identify the rooms that have null values. Does any one out there no how this could be done using SQL 2000 or access I have included a definition of the database below. --Stores the database for the hostel-- create database Ymcadatabase on Primary (Name = 'ymca_data', Filename = 'c:\YDbase\ymca_data.mdf', size = 5mb, Filegrowth = 10%) Log ON (Name = 'ymca_log', FILENAME = 'c:\YDbase\ymca_log.ldf', SIZE = 5mb, FILEGROWTH = 10%) GO The table definitions use Ymcadatabase go create table rooms ( roomNumber char(5) Not Null, block char(50) Not Null, roomStatus char(10) Null, rate money Not Null, Primary Key (roomNumber), ) create table guestInfo ( idNumber char(15) Not null primary key, guestsName varchar(25) not null, guestsurname varchar(25) not null, gender char(10) not null, physicaladdress varchar(30), cellnumber varchar (15) , telephone varchar(15), occupation varchar(15), workNumber varchar(15), sponsorsName varchar(25), sponsorstelephone varchar(15), ) create table users ( userName char (15) Not Null Primary Key, password char(8) Not null, usertype char (10) Not Null, ) create table sessions ( sessionNumber int Identity Not null Primary Key, startdate smalldatetime not null, enddate smalldatetime not null, sessionType char(15) not null, balance money not Null DEFAULT '0', roomNumber char(5) not null references Rooms (roomNumber), idNumber char(15) Not null references guestinfo (idnumber), ) create table accounts ( receiptNumber int Identity Not Null primary key, amountpaid money not Null, datepaid smalldatetime not null, roomNumber char(5) not null references rooms(roomNumber), sessionNumber int Not null references sessions(sessionNumber), paymentType char(10), userName char(15) Not Null references users (username), ) I have also provided some sample data below use Ymcadatabase go insert into rooms(roomNumber,rate,block,roomstatus) values('d1','$2000','d','op') insert into rooms(roomNum
-
I am developing developing a hostel booking system, and I am trying to come up with a query that allows me to find all the rooms that are availiable on a particular day. I have tried using the except statement but it has not work, and I have also tried using a temporary table, but that has not worked. I have also attempted to select all the rooms that are availiable on a particular day and after that I have conducted a left join on the rooms tables, so as to try identify the rooms that have null values. Does any one out there no how this could be done using SQL 2000 or access I have included a definition of the database below. --Stores the database for the hostel-- create database Ymcadatabase on Primary (Name = 'ymca_data', Filename = 'c:\YDbase\ymca_data.mdf', size = 5mb, Filegrowth = 10%) Log ON (Name = 'ymca_log', FILENAME = 'c:\YDbase\ymca_log.ldf', SIZE = 5mb, FILEGROWTH = 10%) GO The table definitions use Ymcadatabase go create table rooms ( roomNumber char(5) Not Null, block char(50) Not Null, roomStatus char(10) Null, rate money Not Null, Primary Key (roomNumber), ) create table guestInfo ( idNumber char(15) Not null primary key, guestsName varchar(25) not null, guestsurname varchar(25) not null, gender char(10) not null, physicaladdress varchar(30), cellnumber varchar (15) , telephone varchar(15), occupation varchar(15), workNumber varchar(15), sponsorsName varchar(25), sponsorstelephone varchar(15), ) create table users ( userName char (15) Not Null Primary Key, password char(8) Not null, usertype char (10) Not Null, ) create table sessions ( sessionNumber int Identity Not null Primary Key, startdate smalldatetime not null, enddate smalldatetime not null, sessionType char(15) not null, balance money not Null DEFAULT '0', roomNumber char(5) not null references Rooms (roomNumber), idNumber char(15) Not null references guestinfo (idnumber), ) create table accounts ( receiptNumber int Identity Not Null primary key, amountpaid money not Null, datepaid smalldatetime not null, roomNumber char(5) not null references rooms(roomNumber), sessionNumber int Not null references sessions(sessionNumber), paymentType char(10), userName char(15) Not Null references users (username), ) I have also provided some sample data below use Ymcadatabase go insert into rooms(roomNumber,rate,block,roomstatus) values('d1','$2000','d','op') insert into rooms(roomNum
select * from rooms where roomNumber not in ( select roomNumber from sessions where '01092007' between startdate and enddate) and roomstatus <> 'blocked' order by roomNumberIf you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".