That works only if I want to display the last photo added. I need to display the last photo added to each category.
Thanks for the help though Emmet -----Original Message----- From: Harley Friedman [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2003 6:22 PM To: CF-Talk Subject: Query Help I'm assuming that all of your ID fields are autoincremented integers. Thus, the newest upload will be the one with the largest GalleryID. Try this: <CFQUERY NAME="getcats" DATASOURCE="#request.ds#"> SELECT Category.CatName, Gallery.directory, subcat.Directory, photos.photo, photos.galimg, subcat.img FROM Category, photos, subcat, Gallery WHERE Category.catID = subcat.CatID AND photos.GalleryID = Gallery.GalleryID AND subcat.subcatID = Gallery.subcatID AND photos.galimg = 1 AND Gallery.GalleryID IN (SELECT MAX(g2.GalleryID) FROM Gallery g2 GROUP BY g2.CategoryID) order by catname </CFQUERY> If your Gallery table only has a subcatID in it (instead of a CategoryID), the subquery would need to be: SELECT MAX(s2.GalleryID) >From Gallery g2, subcat s2 Where s2.subcatID=g2.subcatID GROUP BY s2.CategoryID I don't have SQL Server with me now, so I can't test it, but that should be close. >Maybe someone can help me > >I've written a photo gallery app and I'm having a problem with one of my >queries. My gallery has several Main Categories (category table). >Within each category is a subcategory (subcat table) that contains >photosets (gallery table). After uploading a photoset you choose a photo >that represents that set (galimg). > > >I'm trying to make a page that displays the newest image that represents >its photoset entered into each category. > ><CFQUERY NAME="getcats" DATASOURCE="#request.ds#"> >SELECT Category.CatName, Gallery.directory, subcat.Directory, >photos.photo, photos.galimg, subcat.img >FROM Category, photos, subcat, Gallery >WHERE Category.catID = subcat.CatID AND > photos.GalleryID = Gallery.GalleryID AND > subcat.subcatID = Gallery.subcatID AND > photos.galimg = 1 > order by catname ></CFQUERY> > >This works fine for showing me all the photos that represent their >photoset in each category. I just need the newest photo though. So it >only displays one photo for each category. > >Any help is appreciated. > > >Emmet ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
