Problem with Rewriting Subqueries as Joins
 

Hi friends, I have very little knowledge of JOINs. Please, help me out in rewriting my sub-query to some kind of JOIN for earlier MySQL version

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)

QUERY: Search for the available rooms:

SUBQUERY: This did not work on the remote server

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’
)

ATTEMPTED JOIN: not sure at all if it is correct

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 hope to hear from you soon folks. Thanks

Re: Problem with Rewriting Subqueries as Joins
 

Please don't double post.

Re: Problem with Rewriting Subqueries as Joins
 

----Angelo----- :lol:

My script below is working and i hope same on you....


SELECT table1.*
FROM menu
LEFT JOIN
table2
ON
table1.id = table2.id
WHERE table2.id IS NULL