asked on

MySQL Nested Join select last entry only

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:

SELECT 
	assets.assetID,
	assetStatus.assetStatusName
FROM assets
INNER JOIN (SELECT 
		LNK_AssetsStatus.fk_assetID,
		LNK_AssetsStatus.fk_statusID,						
		assets_status.assetStatusName,
		assets_status.assetStatusColour 
		FROM LNK_AssetsStatus
		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'
GROUP BY
	assets.assetID

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rknevitt

ASKER

HI - THanks for the reply, but i cant figure out how to apply what you have posted, in my situation, also the last link doesnt go anywhere :(

Guy Hengel [angelIII / a3]

please try the first link, it should work ...
anyhow, it's the query with a subquery, doing a WHERE datecol = ( select max(datecol) ,... where ... )
which should be your syntax.

rknevitt

ASKER

I think i have cracked it. Although i couldnt piece it together using the websites you provided, the MAX suggestion put me in the right direction.

This works - however is there a more efficient, "correct" way to do it?

Rob

assets.assetID,
	assets.assetDescription,
	assetStatus.assetStatusName
FROM assets

LEFT JOIN (SELECT 
		ST.fk_assetID,													
		ST.fk_statusID,						
		assets_status.assetStatusName,
		assets_status.assetStatusColour,

		(SELECT 
		LST.fk_statusID
		FROM LNK_AssetsStatus LST
		WHERE LST.fk_assetID = ST.fk_assetID
		AND LST.datetime=(SELECT MAX(datetime)
			FROM LNK_AssetsStatus LST2
			WHERE LST2.fk_assetID = ST.fk_assetID)) statusID


	FROM LNK_AssetsStatus AS ST
	INNER JOIN assets_status ON assets_status.assetStatusID = ST.fk_statusID
	ORDER BY ST.assetStatusID DESC) assetStatus 
ON assetStatus.fk_assetID = assets.assetID	
WHERE assetStatus.statusID = '$statusid'
GROUP BY
	assets.assetID

Open in new window

Guy Hengel [angelIII / a3]

with mysql: not really.
you need to have the proper indexes in place... EXPLAIN PLAN can help you to find any issues.

that said: your original idea with the iscurrent fied is not that bad, could be a real alternative.
also: having the "historical" data in a historical table, hence only the "current" data in the main table would be another.

Guy Hengel [angelIII / a3]

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.