Thanks for the tips, Chad! Rick
> -----Original Message----- > From: Chad Gray [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 21, 2008 1:10 PM > To: CF-Talk > Subject: RE:_What_relationship_am_I_missing_in_this_query ? > > I think you need to move your second query out of the select statement and > into the join section of the > query. > > This chunk of SQL goes and gets the most recent ElementStatusHistoryID by > using a GROUP BY and uses it > to join in the table at that one row that it finds with the GROUP BY. > > Hope it helps. > > SELECT c.CatalogID, c.CatalogName, c.currentStatus, ct.*, e.*, > es.ElementStatus, es.ElementStatusDate, > es.UserName > FROM Catalogs as c > LEFT JOIN Elements as e ON c.CatalogID = e.CatalogID > JOIN CatalogTypes as ct ON c.CatalogTypeID = ct.CatalogTypeID > 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 = #val(URL.CatalogID)# > > > > > -----Original Message----- > > From: Rick Faircloth [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, May 21, 2008 11:58 AM > > To: CF-Talk > > Subject: What relationship am I missing in this query? > > > > Hi, all... > > > > I'm trying to return each property from the properties table, > > along with the *first* photo in the property_photos table for each > > property > > with this query: > > > > select p.property_id, > > (select pp.photo_filename from property_photos pp, > > properties p where pp.photo_mls_number = > > substring_index(p.mls_number, '_', 1) limit 1) > > as prop_photo_filename > > from properties p, property_photos pp > > where substring_index(p.mls_number, '_', 1) = pp.photo_mls_number > > > > However, the query only return the first photo it finds and attaches it > > to every property instead of different photos for each property. > > > > I tried it without the "limit 1" qualification, but then every photo > > for every property is returned. (Which is actually closer to the result > > I'm after...but I only want the first photo for each property) > > > > I just can't see the relationship qualification I'm missing? > > > > Suggestions, anyone? > > > > Thanks, > > > > Rick > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305866 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
