Solved

MySQL Nested Join select last entry only

Posted on 2010-08-31
7
279 Views
Last Modified: 2012-08-14
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
Comment
Question by:rknevitt
  • 4
  • 2
7 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33574424
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
 

Author Comment

by:rknevitt
ID: 33574508
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33574567
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:rknevitt
ID: 33574882
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33575089
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37265895
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now