Unusual SELECT query

[mokeyJoe]

Here's my problem, I hope some of you guys & gals can help?

I have two tables...
1. Vehicles
(veh_reg, veh_make, veh_model, veh_type...)
This is a list of vehicles, each veh_reg is unique.

2. Locations
(veh_reg, location, date)
This is a list of locations, a history of each vehicles whereabouts.

I want to be able to (with one query!) select each vehicle from the vehicles table and the LATEST (by date) location for that vehicle from the locations table.

I thought I could use the MAX function, but I can't seem to get it into a single query, or sub-select query.

Any ideas?

[raf]

Can you use a subselect? (ie version 4.1 or up ?)

Do you need all records from Vehicles (outer join) or only the onse that have matching records in Locations ?

Is this query frequently ran and do you need a fast responsetime ?

[mokeyJoe]

Yes I can use a sub select, I have 4.1 on my development machine but would need to upgrade the production server.

It is an outer join, yes. I need a list of every vehicle regardless of whether or not it has a corresponding location.

The query will be run frequently throughout the day, at the moment I have a workaround in php to display the vehicle list which then runs a seperate query for each location for each vehicle! At the moment the php page takes less than a second to load, which is much better than the 30+ seconds it takes our current ms access system to display!

But it's not a 'neat' solution as I really need to be able to use one query for my download routine (which is used extensively throughout the app) will only accept a single query string.

I have tried a subselect, am not sure if I have the syntax right though and I keep being told that mysql only supports a single column being displayed in the subselect?
Thanks

I have tried the following subselect which I would have thought would work (not perfect though), but MySQL won't allow LIMIT in subselects

SELECT veh_reg, veh_make
FROM vehicles
WHERE vehicles.veh_reg IN
(SELECT location FROM locations ORDER BY date DESC LIMIT 1)

or there's this ...

SELECT veh_reg, veh_make
FROM vehicles
WHERE vehicles.veh_reg IN
(SELECT veh_reg, location, MAX(date) FROM locations GROUP BY veh_reg, date);

Which gives the following error...
ERROR 1240 (21000): Operand should contain 1 column(s)

Any help would be appreciated?