Rick,
You can use FROM and immediately use ON. That's why you have a syntax
error. ON is used with joins.
Ravi.
On Sun, Apr 13, 2008 at 7:23 AM, Rick Faircloth <[EMAIL PROTECTED]>
wrote:
> Thanks for the reply, Azadi.
>
> However, your solution has the same problem I encountered
> with a regular JOIN statement: the "limit 1" part of the
> interior select statement limited my results to only 1 property
> with 1 photo, instead of all properties, but only 1 photo each.
>
> I tried:
>
> select sa.street_number, sa.street_name, sa.city,
> sa.public_remarks_01,
> sa.public_remarks_02, sa.public_remarks_03, sa.list_price
> from smlc_acr sa
> inner join
> select sap.photo_filename, sap.photo_mls_number
> from smlc_acr_photos sap
> on sa.mls_number = sap.photo_mls_number
> order by list_price
>
> but that gives me this error:
>
> "You have an error in your SQL syntax;
> check the manual that corresponds to your MySQL server version
> for the right syntax to use near 'select sap.photo_filename,
> sap.photo_mls_number from smlc_acr_photos sap ' at line 4"
>
> I can't see the error in my syntax. But then, again, I'm not the
> most experienced "join writer", having avoided them when I could.
>
> Thanks, again.
>
> Rick
>
> > -----Original Message-----
> > From: Azadi Saryev [mailto:[EMAIL PROTECTED]
> > Sent: Sunday, April 13, 2008 1:45 AM
> > To: CF-Talk
> > Subject: Re: How to write this query?
> >
> > something like the following (note: mysql syntax):
> >
> > SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01,
> > sa.public_remarks_02, sa.public_remarks_03, sa.list_price,
> SAP_SQ.photo_filename
> > FROM smlc_acr sa INNER JOIN (SELECT photo_filename, photo_mls_number
> FROM smlc_acr_photos sap
> WHERE
> > photo_filename <> '' LIMIT 1) SAP_SQ ON sa.mls_number =
> SAP_SQ.photo_mls_number
> > ORDER BY sa.list_price
> >
> > Azadi Saryev
> > Sabai-dee.com
> > http://www.sabai-dee.com/
> >
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303249
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
