I meant to say "can not" Ravi.
On Sun, Apr 13, 2008 at 7:30 AM, [STS] Ravi Gehlot < [EMAIL PROTECTED]> wrote: > 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:303251 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
