room availability not functioning...someone please give guid

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
polly89
Forum Newbie
Posts: 8
Joined: Thu Mar 18, 2010 9:44 am

room availability not functioning...someone please give guid

Post by polly89 »

i have just create 4 tables like below :

CREATE TABLE customer(
customerID INT NOT NULL AUTO_INCREMENT ,
name VARCHAR( 30 ) ,
address VARCHAR( 30 ) ,
tel_no INT( 15 ) ,
email VARCHAR( 30 ) ,
PRIMARY KEY (customerID)
) ENGINE=INNODB;


CREATE TABLE roomtype(
roomtypeID INT NOT NULL AUTO_INCREMENT ,
roomtype VARCHAR( 30 ) ,
roomprice INT( 30 ) ,
roombed INT( 15 ) ,
PRIMARY KEY ( roomtypeID )
) ENGINE=INNODB;

CREATE TABLE rooms(
roomID INT NOT NULL AUTO_INCREMENT ,
roomtypeID varchar( 30 ) ,
room_no INT( 15 ) ,
PRIMARY KEY ( roomID ) ,
FOREIGN KEY ( roomtypeID ) REFERENCES roomtype( roomtypeID ) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB


CREATE TABLE booking(
bookingID INT NOT NULL AUTO_INCREMENT ,
checkin DATETIME,
checkout DATETIME,
nights INT( 10 ) ,
totalprice INT( 100 ) ,
customerID INT,
roomID INT,
PRIMARY KEY ( bookingID ) ,
FOREIGN KEY ( customerID ) REFERENCES customer( customerID ) ,
FOREIGN KEY ( roomID ) REFERENCES rooms( roomID ) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB

i really got no idea how to only display the roomtype and roomprice from the table roomtype. I do really hope someone can help me as i have spent 5 hours only for this one query (yes..i admit i'm not talented in this stuff..), so please,if there's anyone can give any ideas for me to solve this...
i do appreciate it so much...

below is the query that i'm working on that never success :

select distinct roomtype, roomprice from roomtype where romtypeID IN (
select roomtypeID, roomID from rooms where roomID NOT IN (
select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06"))

when i test it at phpmyadmin, the problem comes from the outter select which is the part "select distinct...".
when i tested it, the subselect works fine..the problems comes from the select distinct part
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: room availability not functioning...someone please give guid

Post by mikosiko »

try something like this (disclosure: no tested live... hence could have some errors :) )

Code: Select all

 
  select a.roomId, a.roomtypeId, a.room_no. b.roomprice, b.roombed
  from rooms a LEFT JOIN roomtype b ON (a.roomtypeId = b.roomTypeId)
  where a.roomId NOT IN (select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06")
 
polly89
Forum Newbie
Posts: 8
Joined: Thu Mar 18, 2010 9:44 am

Re: room availability not functioning...someone please give guid

Post by polly89 »

thanks for mikosiko for the reply...

i finally manage to get the query that produce the result that i wanted:
here it is .

SELECT rt.roomtype, rt.roomprice
FROM roomtype rt
INNER JOIN rooms r ON rt.roomtypeID = r.roomtypeID
WHERE r.roomID NOT
IN (

SELECT roomID
FROM booking
WHERE checkin >= '2010-04-01'
AND checkout <= '2010-04-06'
)
GROUP BY rt.roomtypeID

however, i did really appreciate your help. it gives me some ideas about using join.
thanks again. Cheers! :D
Post Reply