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:308607
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
