Hi, Bruce and thanks for the reply. TOP (1) isn't working. From what I can tell, that's an MSSQL function and MySQL (sorry, should have specified db) doesn't use it. MySQL uses Limit 1 for that functionality, but in this case, I don't want to get just the first community, but every community, but only the first photo in the db for each community.
I've tried using join, but haven't hit upon the correct syntax, yet. Any other ideas? Rick > -----Original Message----- > From: Bruce Sorge [mailto:[EMAIL PROTECTED] > Sent: Monday, March 24, 2008 8:55 PM > To: CF-Talk > Subject: Re: How to limit this query to 1 photo per community? > > This should work: > > select TOP (1) nc.new_community_id, nc.new_community_name, > nc.new_community_short_desc, > ncp.new_community_photo_w150, ncp.new_community_photo_w500 > from new_communities nc, new_community_photos ncp > where nc.new_community_id = ncp.new_community_id > order by nc.new_community_name > > > Bruce > > Rick Faircloth wrote: > > This seems like it ought to be simple, but I'm not > > quite seeing the solution. > > > > How would I adjust this query to select one community > > from the new_communities table > > and only the *first* photo from the new_community_photos table. > > > > Right now I get the community as many times as there are photos for it. > > > > I want *one* community and *one* matching photo. > > > > Here's what I put together and then realized the problem... > > > > select nc.new_community_id, nc.new_community_name, > > nc.new_community_short_desc, > > ncp.new_community_photo_w150, ncp.new_community_photo_w500 > > from new_communities nc, new_community_photos ncp > > where nc.new_community_id = ncp.new_community_id > > order by nc.new_community_name > > > > ??? > > > > 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;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301979 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
