[SOLVED] Problem with SELECT query

[boney alex]

Hi, Im currently building a vehicle rental website.

Im workin on a query which is trying to select potential  vehicles for reservation but only if it satisfies some certain characteristics:

  1) it must be held at the branch (ive been using BranchID = 1 for test purposes)

  2) it must be of a certain make and model (ive been using MakeModelID = 3 for test purposes)

  3) the query must EXCLUDE those vehicles currently reserved. For this I've been using NOT BETWEEN Collect_Date AND Return_Date.

 

Table Vehicle

 

VehicleID  Registration  MakeModelID  BranchID

    1          AE02 FWC          3              1

    2          DY53 WAJ            3              1

 

Table Reservation

 

ReserID  CustID  VehicleID  Collect_Date  Return_Date

    1        1            1          2007-04-04      2007-04-11     

 

I need a query that will show both AE02 FWC and DY53 WAJ if the user selects:

* 3 for the MakeModelID

* 1 for the BranchID

* 2007-04-12 for the collect date

 

And only show DY53 WAJ if the collect date was for example 2007-04-09.

 

Any help would be much appreciated. Thanks. Alex

[MadTechie]

i Guess something like:|

SELECT * WHERE
`Table Vehicle`.`MakeModelID` = '3' AND
`Table Vehicle`.`BranchID`  = '1' AND
`Table Reservation`.`` = '2007-04-12'

 

*untested

[Wildbug]

SELECT syntax

MySQL tutorial

 

SELECT * FROM vehicle
JOIN registration ON vehicle.vehicleID=registration.vehicleID
WHERE makemodelID=3
AND branchID=1
AND NOT (collect_date <= 20070409 AND return_date > 20070409);

[boney alex]

hi wildbug,

 

i tried that code but just like my attempts, it failed to recognise the other vehicle DY53 WAJ, because that isnt in the reservation table.

 

 

[btherl]

If the query is fine apart from not returning cars which are not in the reservations table, you can use a left join

 

SELECT * FROM vehicle
LEFT JOIN registration ON vehicle.vehicleID=registration.vehicleID
WHERE makemodelID=3
AND branchID=1
AND (
   NOT (collect_date <= 20070409 AND return_date > 20070409)
   OR (reserID IS NULL)
);

 

reserID will be null for those rows where there is no data from the reservations (registration?) table.

[boney alex]

thanks btherl

 

think ive sorted it using a very similar query:

SELECT *
FROM Vehicle
WHERE BranchID = '1'
AND MakeModelID = '3'
AND VehicleID NOT 
IN (
SELECT VehicleID
FROM Reservation
WHERE '2007-04-03'
BETWEEN Collection_Date
AND Return_Date)

 

Both seem to return identical results. Thanks for your help!!!