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


Select *
From Room R
Where R.room_no
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


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!

	FROM room AS r
	LEFT JOIN booking AS b
	ON ( r.room_no = b.room_no AND r.room_type = 'single' )
	b.arrivel_date = '2005-04-23'
	b.departure_date = '2005-04-30'