This may or may not be the correct way to do this - if its not - please give me some pointers.
I basically have a table of assets, these assets have a status )(live, offline, recycled etc) the status is stored in a seperate table, which is then joined.
The reason for doing this is that i wanted to keep a history/audit trail of previous status's for each asset, doing this seemed easier than having a status field in the main asset table, then another table for historical entrys.
I have a problem when you filter the query by the status - no matter status you choose, that asset will show up against that status, if there is an entry in the status table.
My initial thoughts where jusyt to have a "iscurrent" flag in the status table, where all historical entries are set to 0, when the status is changed (active one is set to 1) then my nested join would have a WHERE iscurrent = '1'.
However - is there a work around. Heres my code:
INNER JOIN (SELECT
INNER JOIN assets_status ON assets_status.assetStatusID = LNK_AssetsStatus.fk_statusID
ORDER BY LNK_AssetsStatus.assetStatusID DESC) assetStatus
ON assetStatus.fk_assetID = assets.assetID
WHERE fk_statusID = '1'