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/ Rick Faircloth wrote: > Hi, all... > > I've got two tables: > > 1 - list of properties > 2 - photos for those properties > > There is more than one photo per property. > > 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. > > Suggestions? > > Thanks! > > Rick > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:303247 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
