5 replies to this topic

[faith89]

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

[WingedPanther]

You can't select roomtypeID and roomID, and then have roomtypeID be in those TWO fields.

[faith89]

hi wingedpanther, i do really not understand your stetement that
"You can't select roomtypeID and roomID, and then have roomtypeID be in those TWO fields."
i'm sory, i'm still new to the mysql query stuff and i felt that maybe the unfunctionality of my query is because of the wrong syntax or logic. I do hope somoene can knock some sense of the logic into it ...

the situation is that,
i would want the query to first, find the available room the checkin and checkout, then it will display the room available in the based on the table rooms that contains roomID and roomtypeID.
Then, from the roomtypeID(frm table room) as a foreign key to the roomtypeID in the table roomtype, it will display the roomprice and roomtype from roomtype.

The display of the roomprice and roomtype is really important as i want to sent the value of the roomtypeID to the next page of the php form by using id

[WingedPanther]

Look at this one piece at a time:

select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06"

This line returns roomID's where the checkin and checkout are in the date range specified.

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

This line takes the list of roomID's and uses them as an exclusion condition. It then returns a list of pairs: roomtypeID and roomID. Notice that each record returned has TWO values.

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"))

Now you take the PAIRS returned (roomtypeID, roomID), and try to use it as a criteria for a SINGLETON (roomtypeID). That simply won't work! You need to return just roomtypeID in the second select.

[faith89]

finally i managed to get the query that i wanted : here is it...

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

btw, thanks to WingedPanther for replying my post...all the efforst is really apprecated

[WingedPanther]

I'm glad you got it :) One thing to be aware of, in general: subselects can be EVIL! They can severely degrade the performance of your query. There isn't always an option, but I've actually had cases where looping through a dataset in code was faster than the database processing a subselect. I'm not saying don't use them, just know there is a performance consideration.