Thanks for the reply, Greg... I had tried it with the alias, but I still get the same error.
Rick > -----Original Message----- > From: Greg Morphis [mailto:[EMAIL PROTECTED] > Sent: Friday, July 04, 2008 12:15 PM > To: CF-Talk > Subject: Re: Why would this query return this error? > > you have the properties table aliased as 'p'.. > > On Fri, Jul 4, 2008 at 11:10 AM, Rick Faircloth > <[EMAIL PROTECTED]> wrote: > > Hi, all... > > > > Why would this query return this error: > > > > 'Unknown column 'properties.mls_number' in 'on clause' > > > > Here's the query: > > > > <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, > > o.mls, o.office_name, o.display_order, > > min( pp.photo_filename) as prop_photo_filename > > from properties p, offices o, property_photos pp > > > > left join property_photos > > on substring_index(properties.mls_number, '_', 1) = > > property_photos.photo_mls_number > > and p.mls = pp.mls > > > > left join offices > > on p.listing_office_mls_id = o.mls_office_id > > and p.mls = o.mls > > > > where p.property_type <> 'rental' > > > > group by properties.mls_number > > order by offices.display_order > > limit 10 > > > > </cfquery> > > > > 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;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308606 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
