Troubled with Rewriting Subquery as JOINs

[dsgnews]

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

[demo]

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!

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