sql works sometimes, but not others.

[kevin531]

hello, my question is this, i have a piece of sql that works under "cars" but not "tractors" i've simley cut and pasted. i've checked and everything seems correct. the error i get is:
syntax error in join expression.
it errors out to a comma in the first line of the second paragraph, right after "TopUnit" here is my code:

SELECT [TRACTOR MAINTENANCE].[UNIT #], [TRACTOR MAINTENANCE].[DATE OF MAINTENANCE], [TRACTOR MAINTENANCE].MILAGE, [TRACTOR SPEC].MAKE, [TRACTOR SPEC].MODEL, [TRACTOR SPEC].[SERIAL #]

FROM [TRACTOR SPEC] INNER JOIN ([select [UNIT #] as TopUnit, Max([DATE OF MAINTENANCE]) as TopDate from [TRACTOR MAINTENANCE] group by [UNIT #]]. AS Tops INNER JOIN [TRACTOR MAINTENANCE] ON (Tops.TopUnit = [TRACTOR MAINTENANCE].[UNIT #]) AND (Tops.TopDate = [TRACTOR MAINTENANCE].[DATE OF MAINTENANCE])) ON [TRACTOR SPEC].[UNIT NUMBER] = [TRACTOR MAINTENANCE].[UNIT #];

why does it do this? any help would be great, thanks.

 

[rac2]

Is that period really in your query or is it a typo?
[UNIT #]]. AS Tops INNER JOIN

 

[kevin531]

It's in there. I've experimented more and found that if i cut and paste the SQL into a new query that it doesn't work then either. i think what i may not be understanding well is the tops command.

what this query does is finds the latest date, then finds the miles that associate with that date. this way odometer rollovers are new engines are accounted for.

Someone one this formum previouly helped me with it but i still don't understand it. any help would be great. thanks.

-Kevin531

 

[r937]

yeah, i've seen that weird subquery syntax before, enclosed in square brackets and followed by a period, but i've never used it myself

try a correlated subquery like this --

[tt]SELECT maint.[UNIT #]
, maint.[DATE OF MAINTENANCE]
, maint.MILAGE
, spec.MAKE
, spec.MODEL
, spec.[SERIAL #]
FROM [TRACTOR SPEC] spec
INNER
JOIN [TRACTOR MAINTENANCE] maint
ON spec.[UNIT NUMBER] = maint.[UNIT #];
WHERE maint.[DATE OF MAINTENANCE]
= ( select max([DATE OF MAINTENANCE])
from [TRACTOR MAINTENANCE]
where [UNIT #]
= maint.[UNIT #] )[/tt]

rudy

http://rudy.ca/

 

[rac2]

Tops is not a command, it is an alias or name for the subquery. That allows you to refer to the subquery columns such as in the JOIN condition.

So Tops.TopUnit refers to a column in the subquery

select [UNIT #] as TopUnit, Max([DATE OF MAINTENANCE]) as TopDate from [TRACTOR MAINTENANCE] group by [UNIT #]

TopUnit is an alias for the column [UNIT #].