Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

displaying available vehicles for rent, but complex  query doesn't work

Posted on 2005-04-10
1
Medium Priority
?
174 Views
Last Modified: 2013-12-12
have two tables related to this query: car table and reservation table.
In the car table i have:
RegNo
Model
Make
transmission
pickup
price
status
In the reservation table I have :
OrderID
RegNo
username
pickup
dropoff
datePickUp
dateReturn
Model
Make

I have a page called displaycar2.php. In this page i want to show available cars for the dates chosen. But I don't want any cars that are in the reservation table that interfere with those dates.

Just say have car with reg no 05-TN-5445 reserved for the following dates.
12/04/2005 to 14/04/2005
and
15/04/2005 to 18/04/2005

If a person comes along and enters in dates for rental, lets say, pickup = 13/04/05 to the 17/04/2005. the above car option should not be shown, as it is not available between those dates. Only cars that are not reserved for those dates should be shown.

i can post code i have at moment if ye want?



0
Comment
Question by:acslater
1 Comment
 
LVL 4

Accepted Solution

by:
kulifaj earned 2000 total points
ID: 13752322
Use this query to retrieve avalilable cars. [PickUp DATE] and [Return DATE] are the dates entered for rental.

SELECT * FROM car LEFT JOIN reservation
    ON (car.RegNo=reservation.RegNo AND datePickUp<=[Return DATE] AND dateReturn>=[PickUp DATE])
        WHERE OrderID IS NULL
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question