Brad & Ian...
Thanks so much for your replies.
I'm going to keep them handy for reference if the solution
I've worked out doesn't stand up through all the testing,
but I think I've got a solution.
Here's what I've worked up:
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
The "min" function works because the photos are always in numerical order
and (I hope) the first photo should be the primary photo for the property
when only one is show.
I've got a lot more data that needs to be processed by this query, but
I wanted to get something that works in a limited scope before making it
even more complex.
Also, I would normally use the table abbreviations, such as "p.property_id",
but something about this particular syntax is rejected all attempts at
abbreviation.
Thanks, again, for all you help!
Rick
> -----Original Message-----
> From: Ian Skinner [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 21, 2008 6:08 PM
> To: CF-Talk
> Subject: Re: Can't figure out this query! Aaargh!
>
> This is just a pseudo code example based on my Oracle experience but I
> think you have an extra 'table' in there.
>
> I think you are working too hard, this idea came to me when I was trying
> to work you sub select out.
>
> SELECT
> p.property_id,
> FIRST(pp.photo_filename)
>
> FROM
> properties p LEFT JOIN
> property_photos pp ON (pp.mls_number = p.mls_number)
>
> GROUP BY
> p.property_id
>
> P.S. I left out the substring() function for brevity and general
> sympathy for such a convoluted relationship key.
>
> P.P.S I am assuming your database management system (mysql) supports the
> FIRST() SQL aggregate function. It is not one I have used before and I
> do not know how universal it is. If it does not work
> min(pp.photo_filename) would probably work but this could be a different
> image then you want when more then one is available.
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:305863
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
