Well, just FYI...
Here's my final solution. (It turns out that I need another
clause in my where statement... "and p.mls = o.mls"
to make it work:
<cfquery name="get_properties" datasource="#application.dsn#"
cachedWithin="#CreateTimeSpan(0,0,0,0)#">
select p.property_id, p.mls_number, p.street_number, p.street_name,
p.city, p.state, p.remarks, p.property_type, p.list_price,
p.listing_office_mls_id,
o.mls, o.office_name, o.display_order,
min( pp.photo_filename) as prop_photo_filename
from properties p
left join offices o
on p.listing_office_mls_id = o.mls_office_id
left join property_photos pp
on substring_index(p.mls_number, '_', 1) = pp.photo_mls_number
and p.mls = pp.mls
where p.property_type <> 'rental'
and p.mls = o.mls
group by p.mls_number
order by o.display_order
</cfquery>
> -----Original Message-----
> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> Sent: Friday, July 04, 2008 1:08 PM
> To: CF-Talk
> Subject: RE: Why would this query return this error?
>
> If I query for just all the office data and dump it,
> I do get all of the office table info, but I can't get
> it with the query below...
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308613
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
