I am having trouble constructing a query.
I have two tables - table A contains my "systems", and table B contains my "upgrade" records. Each system has a unique ID, so table B can contain multiple entries for each system. Each upgrade record has a unique ID, so the most recent upgrade for each system will have the highest ID number.
What I am trying to do is list all the systems and join return only the latest upgrade record for each system. For example, if my tables contain three systems and four upgrade records as follows:
Table A:
systemID=1, field2=abc1, field3=xyz1
systemID=2, field2=abc2, field3=xyz2
systemID=3, field2=abc3, field3=xyz3
Table B:
upgradeID=1, systemID=1, details=bla1
upgradeID=2, systemID=2, details=bla2
upgradeID=3, systemID=2, details=bla3
upgradeID=4, systemID=1, details=bla4
I would want the query to return:
systemID=1, field2=abc1, field3=xyz1, details=bla4
systemID=2, field2=abc2, field3=xyz2, details=bla3
systemID=3, field2=abc3, field3=xyz3, details=NULL
Any ideas? Can I do this in a query or will I have to store each system's current upgradeID in Table A?
