Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Can't figure out this query! Aaargh!
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56491#305864
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 -----
Author: Brian Kotek
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56491#305859
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
----- Excess quoted text cut - see Original Post for more -----
|
May 24, 2012
|
Latest Fusion Authority Articles
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
