That looks like the right/only way to do it as far as I know.
Mark
-----Original Message-----
From: Chad Gray [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 30, 2006 11:02 AM
To: CF-Talk
Subject: OT: SQL question
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:262233
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
