Troubled with Rewriting Subquery as JOINs

Expand Post »

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

Similar Threads

Joins in MySQL
Problem with Rewriting Subqueries as Joins in Database Design
Problem with Rewriting Subqueries as Joins in MS SQL

dsgnews

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Apr 2005

Permalink

Apr 26th, 2005

Re: Troubled with Rewriting Subquery as JOINs

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!

MySQL Syntax (Toggle Plain Text)
	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'
	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

Reputation Points: 10

Solved Threads: 2

Newbie Poster

Offline

18 posts
since Jan 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: URGENT: UBBThreads MySQL database merge

Next Thread in MySQL Forum Timeline: secure access to customer portal page?

DaniWeb Message

Cancel Changes

User Name
Password