Can you provide a few rows of data from each table? I'll be gladly to help On Fri, Jul 4, 2008 at 12:08 PM, Rick Faircloth <[EMAIL PROTECTED]> wrote: > 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:308609 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
