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...
??? > -----Original Message----- > From: Rick Faircloth [mailto:[EMAIL PROTECTED] > Sent: Friday, July 04, 2008 12:53 PM > To: CF-Talk > Subject: RE: Why would this query return this error? > > Well, I'm not getting an error after removing > ", office o, properties pp" out of the first "from" line > > and creating the property_photos alias in the first "left join" line, > as in "left join property_photos pp" > > and creating the offices alias in the second "left join" line, > as in "left join offices o" > > so the query ends up like this, with all aliases in use: > > <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 > > left join property_photos pp > on substring_index(p.mls_number, '_', 1) = pp.photo_mls_number > and p.mls = pp.mls > > left join offices o > on p.listing_office_mls_id = o.mls_office_id > and p.mls = o.mls > > where p.property_type <> 'rental' > > group by p.mls_number > order by o.display_order > limit 10 > > </cfquery> > > However, the thing that's really my problem now (and was before I ran into > this issue), > is that I'm not getting any of the "office" data returned in the query. > A dump of these 10 records say "empty" for o.mls, o.office_name, and > o.display_order. > > And I've checked the database to make sure the data is there and property > referenced. > I was getting this for quite awhile while working on this app, but now it's > not pulling > in the office data via the query. > > See anything wrong in the query? > > 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:308608 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
