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