By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

{SOLVED} Availability date ranges, using one query

P: 26
Hello,

I am developing a small java web-based car-pool booking system app which interacts with an access database. I am trying to write 2 queries: The first which will specify whether a given car is available on a given date range e.g. from: 1/12/05 to 12/12/05. the second which will run if the first query is unsuccessful e.g. a list of other cars available on the chosen dates. I have been looking at a Microsoft page which I believe may help do what I require but there are 2 separate queries in their example. I would prefer to have one using either a sub query or join, as I don't know of a way of providing Parameters into the second query using SQL, and its quite messy as well! The 2 queries they have given are below (This is based on a hotel reservation system but functionality is the same)

Booking.Arrival is DateTime field
Booking.Checkout is DateTime field

Labelled queryAvailableRooms
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout date] DateTime;
  2.  
  3. SELECT Booking.Room, Booking.Arrival, Booking.Checkout
  4.  
  5. FROM Booking
  6.  
  7. WHERE (((Booking.Arrival) Between [Please enter arrival date] And [Please enter checkout date]-1)) 
  8.  
  9. Or ((([Checkout]-1) Between [Please enter arrival date] And [Please enter checkout date])) 
  10.  
  11. Or (((Booking.Arrival)<[Please enter arrival date]) And (([Checkout]-1)>[Please enter checkout date]-1));
  12.  
The second query uses the first query (as Reserved Rooms)

Expand|Select|Wrap|Line Numbers
  1. SELECT Rooms.RoomNum
  2. FROM Rooms LEFT JOIN queryAvailableRooms ON Rooms.RoomNum=queryAvailableRooms.Room
  3. WHERE (((queryAvailableRooms.Room) Is Null));
  4.  
Ideally I want to know how these 2 queries can be joined into one so that a web form can pass the dates into it as a connection string.

Any help would be greatly appreciated.

Many Thanks

Mark
Nov 13 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I hope this is what you mean...
Expand|Select|Wrap|Line Numbers
  1. SELECT BlahBlah, 
  2. FROM BlahBlah JOIN (other sub query) AS subQName ON BlahBlah.Fld1=subQName.Fld1
  3. etc
Basically, you surround the SELECT subquery in parentheses () and name it (using AS name) then it becomes like any other data source.
A basic, but real life example would be :
Expand|Select|Wrap|Line Numbers
  1. SELECT subQuery.*
  2. FROM (SELECT *
  3. FROM [tblAccounts]) AS subQuery
This would be equivalent to
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [tblAccounts]
Using subqueries like this has the benefit that you have access to the whole of the SQL - therefore you have greater control.
Nov 13 '06 #2

P: 26
Hi Thanks for your reply

I think I sort've already knew that part.

I tried the following code

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Rooms.RoomNum
  3. FROM Rooms LEFT JOIN ( 
  4.  
  5. SELECT Booking.Room, Booking.Arrival, Booking.Checkout
  6.  
  7. FROM Booking
  8.  
  9. WHERE (((Booking.Arrival) Between [Please enter arrival date] And [Please enter checkout date]-1)) 
  10.  
  11. Or ((([Checkout]-1) Between [Please enter arrival date] And [Please enter checkout date])) 
  12.  
  13. Or (((Booking.Arrival)<[Please enter arrival date]) And (([Checkout]-1)>[Please enter checkout date]-1)) 
  14.  
  15. )
  16.  
  17. AS queryAvailableRooms ON Rooms.RoomNum=queryAvailableRooms.Room
  18.  
  19.  
and access just moaned as it does about the expression being too difficult to work out. I've tried to space out the query so it looks ok. I've also left the parameters in for now.

Any extra help, always appreciated

mark
Nov 13 '06 #3

P: 26
Hello again,

I think by a stroke of luck and some more playing, it was simple.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Rooms.RoomNum
  3. FROM Rooms LEFT JOIN (SELECT Booking.Room, Booking.Arrival, Booking.Checkout
  4.  
  5. FROM Booking
  6.  
  7. WHERE (((Booking.Arrival) Between #9/2/1999# And #9/6/1999#-1)) 
  8.  
  9. Or ((([Checkout]-1) Between #9/2/1999# And #9/6/1999#)) 
  10.  
  11. Or (((Booking.Arrival)<#9/2/1999#) And (([Checkout]-1)>#9/6/1999#-1)) 
  12.  
  13. ) AS queryAvailableRooms ON Rooms.RoomNum = queryAvailableRooms.Room WHERE queryAvailableRooms.Room is null
  14.  
  15.  
  16.  
I've done the above query and it seems to work. Ignore the specifics of the dates. They return the correct two rooms that they should! Now I have a framework, to add some car information........

Thanks again, oh and how shall i close this post??!?!
Nov 13 '06 #4

P: 26
tell a lie..........


I've amended my query so that it is like so:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Cars.CarID
  3. FROM Cars LEFT JOIN (SELECT CarBooking.CarID, CarBooking.datefrom,CarBooking.dateto
  4.  
  5. FROM CarBooking 
  6.  
  7. WHERE (((CarBooking.datefrom) Between #12/3/2005# And #12/4/2005#)) 
  8.  
  9. Or ((([CarBooking.dateto]) Between #12/3/2005# And #12/4/2005#)) 
  10.  
  11. Or (((CarBooking.datefrom)<#12/3/2005#) And (([CarBooking.dateto])>#12/4/2005#)) 
  12.  
  13. ) AS queryAvailableCars ON Cars.CarID = queryAvailableCars.CarID
  14. WHERE queryAvailableCars.CarID is null;
  15.  
  16.  
I have the following booking table:

Booking ID, Car ID, date from, date to
1 , 1, 12/01/2005, 15/05/2005
2 , 2, 14/01/2005, 15/02/2005
3 , 3, 13/01/2005, 31/01/2006

I would assume that the only Car to be available is car 2, since its date range is not within the 2 specified in the query,

However the query returns 1 and 2.

do you have any ideas anyone?!?
Nov 13 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm pretty sure the problem lies in the bracketing. I've simplified the query down to the basis requirements. If you paste it into the sql query window in Access the Jet engine will impose further brackets but that's not a problem. Try it and see what result you get.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Cars.CarID
  3. FROM Cars LEFT JOIN (SELECT CarID, datefrom, dateto
  4. FROM CarBooking 
  5. WHERE (datefrom Between #12/3/2005# And #12/4/2005#) 
  6. Or (dateto Between #12/3/2005# And #12/4/2005#) 
  7. Or (datefrom<#12/3/2005# And dateto>#12/4/2005#)) AS queryAvailableCars 
  8. ON Cars.CarID = queryAvailableCars.CarID
  9. WHERE queryAvailableCars.CarID is null;
  10.  
  11.  
Alternatively you could try:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT CarID FROM Cars
  3. WHERE CarID NOT IN (SELECT CarID FROM CarBooking 
  4. WHERE (datefrom Between #12/3/2005# And #12/4/2005#) 
  5. Or (dateto Between #12/3/2005# And #12/4/2005#) 
  6. Or (datefrom<#12/3/2005# And dateto>#12/4/2005#));
  7.  
  8.  
Nov 13 '06 #6

NeoPa
Expert Mod 15k+
P: 31,494
Try :-
Expand|Select|Wrap|Line Numbers
  1. SELECT CarID
  2. FROM Cars LEFT JOIN (SELECT CarID
  3. FROM CarBooking
  4. WHERE ((datefrom<[Please enter checkout date]) 
  5.   AND (dateto>[Please enter arrival date]))) AS subCarBooking
  6. ON Cars.CarID=subCarBooking.CarID
  7. WHERE subCarBooking.CarID IS NOT Null;
Nov 13 '06 #7

P: 26
Hello guys,

The first two attempts at the sql posted by mmccarthy came up with the same issue. that is, Car 1 and car 2 were selected. using neo's code, came up with cars 1 and 3. note that only car two should appear. this got me thinking that the query need only be changed slightly. I tried this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Cars.CarID
  3. FROM Cars LEFT JOIN (SELECT CarID
  4. FROM CarBooking
  5. WHERE ((datefrom<[Please enter checkout date]) 
  6.   AND (dateto>[Please enter arrival date]))) AS subCarBooking
  7. ON Cars.CarID=subCarBooking.CarID
  8. WHERE subCarBooking.CarID IS Null;
  9.  
  10.  
and this worked! I will have to try out other dates for testing and will let you know how it goes.

thanks guys, I'm not a complete noob to sql (having had 1 yrs experience) but this was largely in oracle 9. access doesn't like the same syntax ive found!
Nov 14 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Good catch on the logic:


This one should also work

SELECT CarID FROM Cars
WHERE CarID NOT IN (SELECT CarID FROM CarBooking
WHERE (((datefrom Between #12/3/2005# And #12/4/2005#)
And (dateto Between #12/3/2005# And #12/4/2005#))
Or ((datefrom<#12/3/2005# And dateto>=#12/3/2005#)
Or (datefrom<#12/4/2005# And dateto>=#12/4/2005#))));
Nov 14 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
Thanks guys, I'm not a complete noob to SQL (having had 1 yrs experience) but this was largely in oracle 9. Access doesn't like the same syntax I've found!
No you're not.
I've just reviewed my SQL and noticed two mistakes - both of which you've fixed in your version.
The second mistake was particularly stupid, as it would give the equivalent of an INNER JOIN - Doh!

Well done.

If no-one has any objections, I'll mark this thread as solved.
Nov 14 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.