•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 402,931 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,029 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 1956 | Replies: 1
 |
•
•
Join Date: Apr 2005
Posts: 8
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Please, please, help me out in rewriting my sub-query to some kind of JOIN for earlier MySQL version:
The query should retrive any availble rooms from the database
MY ATTEMPTED SUB-QUERY:
Select *
From Room R
Where R.room_no
NOT
IN(
Select B.room_no
From R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrival_date = ‘2005-03-23’
And B.departure_date = ‘2005-04-30’
)
My ATTEMPTED JOIN: not working correctly
Select *
From room R, booking B
Where R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrivel_date = ‘2005-04-23’
And B.departure_date=’2005-04-30’
I welcome any better ideaa or comments.
For your information, this are the TABLES in my database:
Room (room_ no, room_ type, price)
Guest (guest_ no, Fname, Sname, address)
Booking (room_no, guest_no, Fname, Sname, arrival_date,
departure_date, emp_no, user_name, password)
Employee (emp_no, Fname, Sname, user_name, password)
Admin (admin_no, Fname, Lname, user_name, password)
I hope to hear from you soon folks. Thanks
The query should retrive any availble rooms from the database
MY ATTEMPTED SUB-QUERY:
Select *
From Room R
Where R.room_no
NOT
IN(
Select B.room_no
From R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrival_date = ‘2005-03-23’
And B.departure_date = ‘2005-04-30’
)
My ATTEMPTED JOIN: not working correctly
Select *
From room R, booking B
Where R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrivel_date = ‘2005-04-23’
And B.departure_date=’2005-04-30’
I welcome any better ideaa or comments.
For your information, this are the TABLES in my database:
Room (room_ no, room_ type, price)
Guest (guest_ no, Fname, Sname, address)
Booking (room_no, guest_no, Fname, Sname, arrival_date,
departure_date, emp_no, user_name, password)
Employee (emp_no, Fname, Sname, user_name, password)
Admin (admin_no, Fname, Lname, user_name, password)
I hope to hear from you soon folks. Thanks
•
•
Join Date: Jan 2005
Posts: 18
Reputation:
Rep Power: 4
Solved Threads: 2
Newbie Poster
Hi
I would add a 'group by' and add the date to the return so you know what date the return is telling you about. But without knowing the column types I am a bit lost as to how I would do the grouping / ordering! What I mean is that you can pull stuff from table booking and add it to each row returned from table room!
demo
I would add a 'group by' and add the date to the return so you know what date the return is telling you about. But without knowing the column types I am a bit lost as to how I would do the grouping / ordering! What I mean is that you can pull stuff from table booking and add it to each row returned from table room!
SELECT * FROM room AS r LEFT JOIN booking AS b ON ( r.room_no = b.room_no AND r.room_type = 'single' ) WHERE b.arrivel_date = '2005-04-23' AND b.departure_date = '2005-04-30'
demo
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Similar Threads
- Joins (MySQL)
- Problem with Rewriting Subqueries as Joins (Database Design)
- Problem with Rewriting Subqueries as Joins (MS SQL)
Other Threads in the MySQL Forum
- Previous Thread: URGENT: UBBThreads MySQL database merge
- Next Thread: secure access to customer portal page?
