MySQL Forums
Forum List  »  General

Troubled with Rewriting SubQuery to JOINs
Posted by: dsg news
Date: April 25, 2005 02:50AM

Please, help me out in rewriting my sub-query to some kind of JOIN
for an 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 ideas 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

Options: ReplyQuote


Subject
Written By
Posted
Troubled with Rewriting SubQuery to JOINs
April 25, 2005 02:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.