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
> -----Original Message-----
> From: Brian Kotek [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 21, 2008 6:23 PM
> To: CF-Talk
> Subject: Re: Can't figure out this query! Aaargh!
>
> 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 <[EMAIL PROTECTED]>
> wrote:
>
> > 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:305864
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
