[SOLVED] Advanced Sub-Query Problem

[boney alex]

Hi, Im trying to write a complicated query and Im almost there!! Im building a vehicle rental website and Im working on a search facility whereby users can select a branch location, a collection date and a return date, and all vehicles that are available between those dates, and at that branch are displayed.

 

My vehicles are of different 'makesandmodels'. The query looks at whether there is AT LEAST one vehicle of a certain makeandmodel available between the dates. However, my query always throws a record of NULL fields for the first record.

 

It seems to throw this record of NULL fields for the first makemodel that isnt available between those dates and at that location. For example, if vehicle 3 is the only vehicle with makemodel 1 and vehicle 3 isnt available between the dates, the query will display 3 1 NULL NULL NULL NULL etc. BUT work thereafter.

"SELECT Vehicle.MakeModelID, Vehicle.VehicleID, MakeandModel.Make, MakeandModel.Model, MakeandModel.Specification, MakeandModel.Engine, MakeandModel.Load_Options, MakeandModel.Extras, MakeandModel.DailyRate 
FROM Vehicle LEFT JOIN MakeandModel ON Vehicle.MakeModelID = MakeandModel.MakeModelID 
AND VehicleID NOT IN (SELECT VehicleID FROM Reservation WHERE '$collectiondate' BETWEEN Collection_Date AND Return_Date) 
AND VehicleID NOT IN (SELECT VehicleID FROM Reservation WHERE '$returndate' BETWEEN Collection_Date AND Return_Date) 
AND VehicleID NOT IN (SELECT VehicleID FROM Rental WHERE '$collectiondate' BETWEEN Collection_Date AND Return_Date) 
AND VehicleID NOT IN (SELECT VehicleID FROM Rental WHERE '$returndate' BETWEEN Collection_Date AND Return_Date) 
AND VehicleID NOT IN (SELECT VehicleID FROM Reservation WHERE Collection_Date AND Return_Date BETWEEN '$collectiondate' AND '$returndate') AND VehicleID NOT IN (SELECT VehicleID FROM Rental WHERE Collection_Date AND Return_Date BETWEEN '$collectiondate' AND '$returndate') 
WHERE Vehicle.BranchID = '$branchID' 
GROUP BY MakeandModel.MakeModelID 
ORDER BY MakeandModel.DailyRate"

[Barand]

That's the nature of a LEFT JOIN

 

With "vehicle LEFT JOIN makeandmodel" if no matching record is found in makeandmodel then there are no values so null values are returned.

 

There's some dodgy code in there too.

 

WHERE Collection_Date AND Return_Date BETWEEN '$collectiondate' AND '$returndate'

should be

WHERE Collection_Date  BETWEEN '$collectiondate' AND '$returndate' AND Return_Date BETWEEN '$collectiondate' AND '$returndate'

 

And isn't there a 4th condition where a vehicle isn't available?

[pre]

                          $collectiondate                    $returndate

                                |                                |

cond 1          C---------------------R                            |

                                |                                |

cond 2                          |                    C---------------------R

                                |                                |

cond 2                          |    C---------------------R    |

                                |                                |

cond 4                  C-------------------------------------------------R

                                |                                |

[/pre]

[boney alex]

Hi Barand, yeah Ive sorted my first problem, for some reason I rearranged the query and put the WHERE clause above all the sub-queries and it worked fine. BUT you are right about that fourth condition, how would you write that as a sub-query if you dont mind me asking?

 

cheers

[Barand]

The first three conditions are covered by

 

WHERE ((collectiondate BETWEEN '$collectiondate' AND '$returndate') OR (returndate BETWEEN '$collectiondate' AND '$returndate'))

 

The 4th condition is

 

WHERE ((collectiondate <= '$collectiondate') AND (returndate >= '$returndate))

 

so you have

SELECT Vehicle.MakeModelID, Vehicle.VehicleID, MakeandModel.Make, MakeandModel.Model,
     MakeandModel.Specification, MakeandModel.Engine, MakeandModel.Load_Options, 
     MakeandModel.Extras, MakeandModel.DailyRate 
FROM Vehicle LEFT JOIN MakeandModel ON Vehicle.MakeModelID = MakeandModel.MakeModelID 
WHERE Vehicle.BranchID = '$branchID'
AND VehicleID NOT IN (SELECT VehicleID FROM Reservation WHERE
              ((collectiondate BETWEEN '$collectiondate' AND '$returndate') OR (returndate BETWEEN '$collectiondate' AND '$returndate'))
              OR ((collectiondate <= '$collectiondate') AND (returndate >= '$returndate)) )
AND VehicleID NOT IN (SELECT VehicleID FROM Rental WHERE
              ((collectiondate BETWEEN '$collectiondate' AND '$returndate') OR (returndate BETWEEN '$collectiondate' AND '$returndate'))
              OR ((collectiondate <= '$collectiondate') AND (returndate >= '$returndate)) )
ORDER BY MakeandModel.MakeModelID, MakeandModel.DailyRate