Seems like this should be so easy!!!!!! This is complicated by the fact that I have to use the second select because of the way the data is structured from the vendors who are supplying it.
Ok... deep breath... Properties table. Property_Photos table. One property in the properties table with many photos for that property in the Property_Photos table. Just trying to run a query that returns each property with the first matching photo in the Property_Photos table (or just returns the property if no photos is present) Property - First Property Photo Property - First Property Photo Property - No Photo Property - First Property Photo etc. I tried this: select p.property_id from properties p, property_photos pp left join (select pp.photo_filename from property_photos pp, properties p where pp.photo_mls_number = substring_index(p.mls_number, '_', 1) limit 1) as photos on pp.photo_mls_number = substring_index(p.mls_number, '_', 1) but in my query builder I get the error "1054 - Unknown column 'p.mls_number' in 'on clause' and I can't figure out how to help MySQL 5 "know" the 'p.mls_number' column. Suggestions? 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:305844 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
