room availability not functioning...someone please give guidance..

[pollysal]

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