Related Tables: Help Needed With JOIN Query

[Rommel the iCeMAn]

Hi Group,

My apologies for the lengthy post, but here goes...

I have the following tables:

TABLE Vehicles
(
[ID] nvarchar(5),
[Make] nvarchar(20),
[Model] nvarchar(20),
)

TABLE [Vehicle Status]
(
[ID] int, /* this is an auto-incrementing field*/
[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */
[Status] nvarchar(20),
[Status Date] datetime
)

Here's my problem...

I have the following data in my [Vehicles] and [Vehicle Status] tables:

[ID] [Make] [Model]
----------------------
H80 Nissan Skyline
H86 Toyota Aristo
[ID] [Vehicle ID] [Status] [Status Date]
----------------------------------------
1 H80 OK 2006-10-01
2 H80 Damage 2006-10-05
3 H86 OK 2006-10-13
4 H86 Dent 2006-10-15
5 H86 Scratched 2006-10-16

I need a query that will join the two tables so that the most recent
status of each vehicle can be determined. I've gotten as far as:

SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM
[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [Vehicle
Status].[Vehicle ID]

Of course this produces the following results:

[ID] [Make] [Model] [Status] [Status Date]
--------------------------------------------
H80 Nissan Skyline OK 2006-10-01
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo OK 2006-10-13
H86 Toyota Aristo Dent 2006-10-15
H86 Toyota Aristo Scratched 2006-10-16

How do I filter these results so that I get only the MOST RECENT vehicle
status?

i.e:

[ID] [Make] [Model] [Status] [Status Date]
--------------------------------------------
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo Scratched 2006-10-16

Thanks in advance,
Rommel the iCeMAn

*** Sent via Developersdex http://www.developersdex.com ***

[nigelrivett]

SELECT v.[ID], Make, Model, [Status], [Status Date]
FROM
[Vehicles] v INNER JOIN [Vehicle Status] vs ON v.[ID] = vs.[Vehicle ID]
and vs.[Status Date] = (select max(vs2.[Status Date]) from [Vehicle
Status] vs2 where vs2.[Vehicle ID] = vs.[Vehicle ID])

www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***