House of Fusion
Home of the ColdFusion Community
Hostmysite VPS Hosting

Search cf-talk

August 20, 2008

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
           1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31             

Subscribe Now
Fusion Authority Quarterly Update - ColdFusion 8 Special Edition

For ColdFusion hosting try HostMySite.com.
Search over 2,500 ColdFusion resources here  >>>      
Home /  Groups /  ColdFusion Talk (CF-Talk)

Can't figure out this query! Aaargh!

  << Previous Post |  RSS |  Sort Oldest First |  Sort Latest First |  Subscribe to this Group Next >> 
Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Brian Kotek
05/21/2008 06:24 PM

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 -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Rick Faircloth
05/21/2008 07:43 PM

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 -----


<< Previous Thread Today's Threads Next Thread >>

Mailing Lists