0

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

3
Contributors
3
Replies
4
Views
12 Years
Discussion Span
Last Post by relawson
0

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

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.