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
room availability not functioning...someone please give guid
Moderator: General Moderators
Re: room availability not functioning...someone please give guid
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")
Re: room availability not functioning...someone please give guid
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!
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!