Hi, Jim, et al...
No sort order field, but this solution is working well:
select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01,
sa.public_remarks_02, sa.public_remarks_03, sa.list_price,
(select sap.photo_filename
from smlc_acr_photos sap
where sap.photo_mls_number = sa.mls_number limit 1)
as photo_filename
from smlc_acr sa
order by sa.list_price
Thanks everyone for the help!
Rick
> -----Original Message-----
> From: Jim Wright [mailto:[EMAIL PROTECTED]
> Sent: Sunday, April 13, 2008 10:42 AM
> To: CF-Talk
> Subject: Re: How to write this query?
>
> On Sun, Apr 13, 2008 at 8:27 AM, Kris Jones <[EMAIL PROTECTED]> wrote:
> > How about something like:
> >
> > select sa.street_number, sa.street_name, sa.city,
> > sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03,
> > sa.list_price,
> > (select top 1 sap.photo_filename from smlc_acr_photos sap where
> > sap.photo_mls_number = sa.mls_number) as photo_filename
> > from smlc_acr sa
> > order by sa.list_price
> >
> > The only thing you may want to consider here is to include an order by
> > on the subquery so that you get the "first" photo you want.
> >
>
> If you have a unique and predictable (1,2,3) order by field, you could
> also do something like:
>
> SELECT sa.street_number, sa.street_name, sa.city,
> sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03,
> sa.list_price,p.photo_filename
> FROM smlc_acr sa LEFT JOIN smlc_acr_photos p ON p.photo_mls_number =
> sa.mls_number AND p.sortorder = 1
> ORDER BY sa.list_price
>
> OR
>
> SELECT sa.street_number, sa.street_name, sa.city,
> sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03,
> sa.list_price,p.photo_filename
> FROM smlc_acr sa LEFT JOIN (SELECT photo_mls_number,photo_filename
> FROM smlc_acr_photos WHERE sortorder = 1) p ON p.photo_mls_number =
> sa.mls_number
> ORDER BY sa.list_price
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:303261
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
