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