Problem with Rewriting Subqueries as Joins

[dsgnews]

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 versios.

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

[deeraj]

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 versios.

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

In the first place your subquery is wrong.
You have to write

Select *
From Room R
Where R.room_no
IN ( Select B.room_no
From booking B
Where B.arrival_date = ‘2005-03-23’
And B.departure_date = ‘2005-04-30’
)
and R.room_type = ‘single’

Here you have specified "not in" but in the join u have equated the room nos of the two tables. That is exactly opposite of wat u have done with the subquery.

Select R.*
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’ ;

[relawson]

First, you need to add some unique primary keys. That will make joining much easier and the code more readable. Look up "normalization" in google and try to get to 3NF. Second, use "not exists" instead of "not in". They both work, but "not in" does a table scan. If your database gets large, that isn't going to work quickly. Not a big deal for lookup tables and small databases, just FYI.

Now for the join:

You tried this:

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

Try something like this:

Select * From Room R
Join Booking B
On R.room_no = B.room_no
Where R.room_type = ‘single’
And B.arrivel_date = ‘2005-04-23’
And B.departure_date=’2005-04-30’

Also, look up on Google the different types of joins and use what you want. Inner is the default in MS SQL; I should have been explicit in my code but now you know. Good Luck.

[relawson]

Ooops. I should have also specified R.* in my select. Will get you every time :-)