Join Query

[Jigs]

Hi All,

I have 2 tables. [1] AvailableCars [2] CarPics

[1] AvailableCars consists of fields as Zone (eg. ZoneA, ZoneB, ZoneC) , CarCategory (eg. CarAA1, CarAB1, CarAA2 ... and so on), DailyRates, and Valid DateBounds (FromDate, ToDate).

[2] CarPics consists of CarCategory and Carpicture (Path of the car image).

Now I want to Bring Only those cars from the AvailableCars table and CarPics which satisfy the criteria of falling between Valid Date Bounds...I am using join as the following :

SELECT
AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY, AVAILABLECARS.DAILYRATES, AVAILABLECARS.FROMDATE, AVAILABLECARS.TODATE, CARPICS.CARPICTURE
FROM CARPICS
JOIN AVAILABLECARS.CARCATEGORY = CARPICS.CARCATEGORY
WHERE
AVAILABLECARS.ZONECODE = "ZONEA2XY" AND
AVAILABLECARS.FROMDATE BETWEEN "DATEVAR1" AND "DATEVAR2"
ORDERBY AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY

Using above query, I am not able to get the desired results.

Can Any body help me in solving this problem ?

Thanks in Advance,

Regards,

Jigs

 

[aleb]

Where does the data for datevar1 and datevar2 come from ?

 

[Jigs]

Consider DateVar1 and dateVar2 as two ColdFusion Date variables which consists of valid dates as FromDate and ToDate.

 

[aleb]

I assume the SQL for the query shold look like this.


PARAMETERS Datevar1 DateTime, Datevar2 DateTime;
SELECT AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY, AVAILABLECARS.DAILYRATES, AVAILABLECARS.FROMDATE, AVAILABLECARS.TODATE, CARPICS.CARPICTURE
FROM CARPICS INNER JOIN AvailableCars ON AVAILABLECARS.CARCATEGORY = CARPICS.CARCATEGORY
WHERE (((AVAILABLECARS.ZONECODE) = "ZONEA2XY") And ((AvailableCars.FROMDATE ) Between [datevar1] And [datevar2]));