SQL Apprentice Question
My question concerns three tables:
- tblHeader - containing general information such as product ID, description
lstLocations - lookup table containing location IDs
tblLocationHistory - a transaction table containing the location history of
each header and the date of their installation.
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?
FROM dbo.lstLocations INNER JOIN
dbo.tblLocationHistory ON dbo.lstLocations.Location_ID
= dbo.tblLocationHistory.LocationID INNER JOIN
dbo.tblHeader ON dbo.tblLocationHistory.HeaderID =
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
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),
>> 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