BorlandTalk.com

davout · Guest

I'm using MySQL 4.0.16

How do I join two tables - one a master table, the second a detail table holding multiple status entries for each master table entry - so that a query can return the master table fields plus the latest status fields (the status entry with the most recent date)?

table : station
Fields: StationID INTEGER, StationName VARCHAR

e.g.
1, London
2 ,Brighton

table: stationstatus
Fields: StationID, StatusDate, Metric1,Metric2,Metric3

e.g.

1, 23/8/04, 1, 1, 1
1, 24/8/04, 2, 2, 2
1, 25/8/04, 3, 3, 3
2, 23/8/04, 4, 4, 4
2, 24/8/04, 5, 5, 5
2, 25/8/04, 6, 6, 6

What I'm looking for is a query that returns

StationID,StationName,StatusDate,Metric1,Metric2,Metric3

e.g.

1, London, 3, 3, 3,
2, Brighton, 6, 6, 6

Any ideas?