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