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