SQL Join question - getting most recent value in a history table

SQL Apprentice Question
My question concerns three tables:


- tblHeader - containing general information such as product ID, description
etc
HeaderID
HeaderDescription
HeaderSerialNumber


lstLocations - lookup table containing location IDs
LocID
LocDescription


tblLocationHistory - a transaction table containing the location history of
each header and the date of their installation.
HistoryID
HeaderID
LocID
InstallDate


What I'm after is a select which shows each line in tblHeader and its most
recent location. However my first attempt (as you would expect from SQL)
returns every product and every location they have been in - e.g. Product X
has 7 locations - thus appears 7 times in the results from the below query.
Unfortunately I'm after the most recent location of each Header *only*.


I feel like this is something I should be able to do with my eyes closed but
I can't seem to crack it. Please could someone point me in the right
direction! Should I be looking at using UDF's for example?




*******************


SELECT
dbo.tblHeader.HeaderID,
dbo.tblHeader.Type,
dbo.tblHeader.SerialNumber,
dbo.lstLocations.Location_Name,
dbo.tblLocationHistory.InstallDate
FROM dbo.lstLocations INNER JOIN
dbo.tblLocationHistory ON dbo.lstLocations.Location_ID
= dbo.tblLocationHistory.LocationID INNER JOIN
dbo.tblHeader ON dbo.tblLocationHistory.HeaderID =
dbo.tblHeader.HeaderID


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You also need to learn ISO-11179 rules so you will stop using those
silly redundant affixes that describe physical implementations in an
RDBMS. It makes you look like a BASIC programmer. Also, tables are
sets which usually have more than one element, so you need to use
collecitve or plural names.


The proper way to model temporal data is with duration, not chronons
(google it). Try this:


CREATE TABLE LocationHistory
(header_id INTEGER NOT NULL
REFERENCES Headers (header_id),
loc_id INTEGER NOT NULL
REFERENCES Locations (loc_id),
start_date DATETIME NOT NULL.
end_date DATETIME, -- null is current location
PRIMARY KEY (header_id, loc_id, start_date),
etc.);



>> What I'm after is a select which shows each line in Headers and its most recent location. <<


Look for "end_date IS NULL" in your query. Proper DDL means much
easier coding.