I am not a MS SQL guru and this bit of SQL is about as advanced as I get.
I am basically doing a query on a table of catalogs and elements inside of the
catalog. Each element has a history of status changes (new, in progress, done
etc).
I want to get the most current status for the element from the Statushistory
table so I use MAX() on the UID of the history table (see the second left join).
Then I take that max UID and use it to look up the information on the Status
(see the third left join).
Is there a better way of writing this? If there are a lot of elements (100 or
more) then the query can take a couple of seconds to perform.
SELECT c.CatalogID, c.CatalogName, e.*, es.ElementStatus, es.ElementStatusDate,
es.UserName
FROM Catalogs as c
LEFT JOIN Elements as e ON c.CatalogID = e.CatalogID
LEFT JOIN (
SELECT MAX(ElementStatusHistoryID) as MaxESHID, ElementID
FROM ElementStatusHistory
GROUP BY ElementID
) as maxESH ON (e.ElementID = maxESH.ElementID)
LEFT JOIN (
SELECT ElementStatusHistoryID, ElementStatus, ElementStatusDate,
UserName
FROM ElementStatusHistory
) as es ON (es.ElementStatusHistoryID = maxESH.MaxESHID)
WHERE c.CatalogID = 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262221
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
