473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

room availability not functioning

17
i have just create 4 tables like below :

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE customer(
  2. customerID INT NOT NULL AUTO_INCREMENT ,
  3. name VARCHAR( 30 ) ,
  4. address VARCHAR( 30 ) ,
  5. tel_no INT( 15 ) ,
  6. email VARCHAR( 30 ) ,
  7. PRIMARY KEY (customerID)
  8. ) ENGINE=INNODB;
  9.  
  10.  
  11.  CREATE TABLE roomtype(
  12. roomtypeID INT NOT NULL AUTO_INCREMENT ,
  13. roomtype VARCHAR( 30 ) ,
  14. roomprice INT( 30 ) ,
  15. roombed INT( 15 ) ,
  16. PRIMARY KEY ( roomtypeID )
  17. ) ENGINE=INNODB;
  18.  
  19.  CREATE TABLE rooms(
  20. roomID INT NOT NULL AUTO_INCREMENT ,
  21. roomtypeID varchar( 30 ) ,
  22. room_no INT( 15 ) ,
  23. PRIMARY KEY ( roomID ) ,
  24. FOREIGN KEY ( roomtypeID ) REFERENCES roomtype( roomtypeID ) ON UPDATE CASCADE ON DELETE CASCADE
  25. ) ENGINE = INNODB 
  26.  
  27.  
  28. CREATE TABLE booking(
  29. bookingID INT NOT NULL AUTO_INCREMENT ,
  30. checkin DATETIME,
  31. checkout DATETIME,
  32. nights INT( 10 ) ,
  33. totalprice INT( 100 ) ,
  34. customerID INT,
  35. roomID INT,
  36. PRIMARY KEY ( bookingID ) ,
  37. FOREIGN KEY ( customerID ) REFERENCES customer( customerID ) ,
  38. FOREIGN KEY ( roomID ) REFERENCES rooms( roomID ) ON UPDATE CASCADE ON DELETE CASCADE
  39. ) 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 :

Expand|Select|Wrap|Line Numbers
  1. select distinct roomtype, roomprice from roomtype where romtypeID IN (
  2. select roomtypeID, roomID from rooms where roomID NOT IN (
  3. 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
Mar 23 '10 #1

✓ answered by Mayur2007

Hello

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

Regards,
Mayur Bhayani

2 2067
Hello

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

Regards,
Mayur Bhayani
Mar 31 '10 #2
digituf
17
actually i already solved the problems..however thanks for the tips..:)
Apr 1 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Dave Robinson | last post by:
I was wondering if anyone could help me with a problem I'm having. I've been using Dreamweaver to create a hotel booking system for a friend of mine, using MySQL (version 4.0.21) and PHP 5. The...
0
by: Joan MacEachern | last post by:
Date: Thursday, October 2, 2003 Time: 10-11am PT/ 1-2pm ET Some business applications define mission critical. You just can't afford to have them go down. Ever. Is 'always on' a realistic...
9
by: Dan Williams | last post by:
Can anyone tell me how to go about creating my own custom ASP page that can show several different peoples availability from their Outlook/Exchange 2000 calendars? I don't want to have to ask...
2
by: Andy | last post by:
Hi folks I teach. At school, four IT rooms are booked using a paper based outline timetable. Completing it is easy but basic and impossible to ensure completion of all fields (name, year...
4
by: John Coen | last post by:
Help, does anyone have a database I could use to record downtime on multiple systems, that will also show me availability percentages on a day to day basis worked against actual service hours. If...
4
by: Jeremy | last post by:
How can I prove uptime (or quantify downtime) on an ASP.NET 1.1 Web application (running on an IIS6 Windows Server Standard server)? What's going on here is that I'm considering the feasability...
5
by: Steven Blair | last post by:
My problem is trying to calcuate whether a room is booked during a date period. I have a table with two columns (Start and End date). I need some SQL code to calculate whether a room is booked...
1
by: ALIABBAS J PETIWALA | last post by:
N -ROOM LIGHTS PROBLEM ======================== THERE IS A BIG SQURE ROOM OF SIDE N WHICH CONSISTS OF N X N SMALLER SQUARE ROOMS(ARRANGED LIKE CHESS BOARD) EACH ROOM HAS A LIGHT. WHEN the...
1
by: georgewbaba | last post by:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE> New Document </TITLE> </HEAD> <script type="text/javascript"> function addpath(obj ,vari){ ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.