• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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

0
rknevitt
Asked:
rknevitt
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you indeed need to find the "last" entry in the history, per ID ...

you shall find the answer in this article:
http://www.experts-exchange.com/A_3203.html
http://www.experts-exchange.com/A_3203-DISTINCT-vs-GROUP-BY...-and-why-does-it-not-work-for-my-query?.html
0
 
rknevittAuthor Commented:
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 :(
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
rknevittAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now