Chad Gray wrote:
>
> 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).
So each element always has a corresponding value in the history table.
> 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
Try:
SELECT
c.CatalogID,
c.CatalogName,
e.*, es.ElementStatus,
es. ElementStatusDate,
es.UserName
FROM
Catalogs C
LEFT JOIN
(
SELECT *
FROM
(
SELECT MAX(ElementStatusHistoryID) as MaxESHID,
ElementID
FROM ElementStatusHistory
GROUP BY ElementID
) tmp
INNER JOIN
ElementStatusHistory ON es.ElementStatusHistoryID =
tmp.MaxESHID
INNER JOIN
Elements ON e.ElementID = tmp.ElementID
) E ON C.CatalogID = E.CatalogID
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:262232
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
