Troubled with Rewriting Subquery as JOINs

Reply

Join Date: Apr 2005
Posts: 8
Reputation: dsgnews is an unknown quantity at this point 
Solved Threads: 0
dsgnews dsgnews is offline Offline
Newbie Poster

Troubled with Rewriting Subquery as JOINs

 
0
  #1
Apr 25th, 2005
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
Reply With Quote Quick reply to this message  
Join Date: Jan 2005
Posts: 18
Reputation: demo is an unknown quantity at this point 
Solved Threads: 2
demo demo is offline Offline
Newbie Poster

Re: Troubled with Rewriting Subquery as JOINs

 
0
  #2
Apr 26th, 2005
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!

  1. SELECT
  2. *
  3. FROM room AS r
  4. LEFT JOIN booking AS b
  5. ON ( r.room_no = b.room_no AND r.room_type = 'single' )
  6. WHERE
  7. b.arrivel_date = '2005-04-23'
  8. AND
  9. b.departure_date = '2005-04-30'

demo
Reply With Quote Quick reply to this message  
Reply

This thread is more than three months old.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC