Can't figure out this query! Aaargh!

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 <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Thanks for the tips and code, Brian! I finally did work up a solution that (hopefully) will hold up under testing.  I will keep your solution handy, however! Here's what's working in my preliminary tests:    select properties.property_id, properties.mls_number, min( property_photos.photo_filename) as prop_photo_filename      from properties left join property_photos        on substring_index(properties.mls_number, '_', 1) = property_photos.photo_mls_number       and properties.mls = property_photos.mls group by properties.mls_number Rick ----- Excess quoted text cut - see Original Post for more -----