Can't figure out this query! Aaargh!

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

I've been searching everywhere for this solution...but nothing has worked. Anyone? ----- Excess quoted text cut - see Original Post for more -----

This is just a pseudo code example based on my Oracle experience but I think you have an extra 'table' in there. I think you are working too hard, this idea came to me when I was trying to work you sub select out. SELECT   p.property_id,   FIRST(pp.photo_filename) FROM   properties p LEFT JOIN   property_photos pp ON (pp.mls_number = p.mls_number) GROUP BY   p.property_id P.S.  I left out the substring() function for brevity and general sympathy for such a convoluted relationship key. P.P.S I am assuming your database management system (mysql) supports the FIRST() SQL aggregate function.  It is not one I have used before and I do not know how universal it is.  If it does not work min(pp.photo_filename) would probably work but this could be a different image then you want when more then one is available.

Brad & Ian... Thanks so much for your replies. I'm going to keep them handy for reference if the solution I've worked out doesn't stand up through all the testing, but I think I've got a solution. Here's what I've worked up:    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 The "min" function works because the photos are always in numerical order and (I hope) the first photo should be the primary photo for the property when only one is show. I've got a lot more data that needs to be processed by this query, but I wanted to get something that works in a limited scope before making it even more complex. Also, I would normally use the table abbreviations, such as "p.property_id", but something about this particular syntax is rejected all attempts at abbreviation. Thanks, again, for all you help! Rick ----- Excess quoted text cut - see Original Post for more -----

I was going to reply earlier, but I am not that familiar with MySQL syntax. There are several ways to accomplish this in MS SQL at least.  Hopefully they work in MySQL as well. I'm not 100% following your SQL below... 1) You could place a nested limit 1 select from the photos table in the select list from the properties table. 2) Does your photos table have an item sort column to denote which picture is first, second, third?  If so join the tables something like mls number and sort_order = 1 3) If you wanted to left join the photos table as a derived table, you would need to group the sub select by MLS number, outputting the MLS number and use some aggregate function like max() to get a random photo for that MLS.  Make sure when you join your derived table, you use the proper alias. 4) Even simpler than option three would be to join the two tables on MLS and then group by everything in the properries table, and use an aggregate function to get the max photo. 4) You could do an initial insert into a temp table of just MLS listings.  Then do an update to that table from the photos table joining on MLS number.  If there are multiple photos, only one will get put in the temp table.  (This is probably my favorite, because it is verbose, and probably performs best) 5) If you are dealing with millions of records and the select really needs to scream, place a denormalized column in the properties table which holds the first photo.  If any changes to the photo table were made, the properties table would need to be updated obviously.  This becomes a problem only if updates/inserts > reads. 6) And my absolute worst idea yet-- join the tables on MLS and return ALL photos to CF ordered by MLS.  cfoutput with a group attribute on MLS and place some code in the inner cfoutput to ignore repeated photos for a given MLS. Hope that gives you something.  I can't guarantee each of those will work in MySQL, but they should in MSSQL. ~Brad I've been searching everywhere for this solution...but nothing has worked. Anyone?