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:305829
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
