Expand|Select|Wrap|Line Numbers
- 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 do appreciate it so much...
below is the query that i'm working on that never success :
Expand|Select|Wrap|Line Numbers
- 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 tested it, the subselect works fine..the problems comes from the select distinct part