It's probably because it looks like your join syntax is wrong. You're telling the database:
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) and your ON statement is not doing anything with the photos subquery. You might try something like: select p.property_id, photos.photofilename from properties p left join property_photos pp on pp.photo_mls_number = substring_index(p.mls_number, '_', 1) left join (select pp2.photo_mls_number, pp2.photo_filename from property_photos pp2 where pp2.photo_mls_number = pp.photo_mls_number limit 1 ) photo on photo.photo_mls_number = pp.photo_mls_number On Wed, May 21, 2008 at 3:28 PM, Rick Faircloth <[EMAIL PROTECTED]> wrote: > 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:305859 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
