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. Cheers, Kris > What I want to do is get each property and the first > photo for that property. > > Here's what I've tried: > > select distinct sa.street_number, sa.street_name, sa.city, > sa.public_remarks_01, > sa.public_remarks_02, sa.public_remarks_03, > sa.list_price, sap.photo_filename > from smlc_acr sa, smlc_acr_photos sap > where sa.mls_number = sap.photo_mls_number > order by list_price > > That doesn't work, because each row is distinct because of the new photo. > > I tried joins, but couldn't get that to work. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:303253 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
