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 -----Original Message----- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 4:44 PM To: CF-Talk Subject: RE: Can't figure out this query! Aaargh! I've been searching everywhere for this solution...but nothing has worked. Anyone? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:305856 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
