room availability not functioning

[digituf]

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

[Mayur2007]

Hello

Try to use "group by" instead of "select distinct"

Regards,
Mayur Bhayani

[digituf]

actually i already solved the problems..howev er thanks for the tips..:)