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

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