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