Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mr Fett
Starting Member
28 Posts |
 Posted - 2008-10-06 : 18:40:30
|
Hi all,Apologies for returning again so quickly - believe me its not for want of Googling: I'm building a complex project in a short time span!This I believe will be a simple on for the brains-that-be.I have a list of galleries which each contain a list of photos. I want to return the galleries and any ONE photo row for that gallery. So:GALLERY_galleries-----------------galleryIDgalleryNameGALLERY_photos--------------photoIDgalleryIDphotoFileNameBased on my previous experiences, I tried to achieve this with the GROUP BY galleryID clause but couldn't return the photoFileName because any items not in the group clause must be in an aggregate function ( and LAST AND FIRST don't work in SQL Server I discovered!).I then moved on to sub queries but it seems very clunky and returns galleries that contain no photos:SELECT galleryID, galleryName,(SELECT TOP 1 photoFileName FROM GALLERY_photos WHERE galleryID = GALLERY_galleries.galleryID) AS photoFileName,FROM GALLERY_galleries WHERE (websiteID = 12) I tried adding " AND (photoFileNameIS NOT NULL) " is not null to the end but I get an error saying "Invalid Column Name".I'm sure there must be an elegant way to solve what I imagine is a common issue - does any one have any ideas?Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-07 : 01:03:13
|
| [code]SELECT g.galleryID,g.galleryName,p.photoFileNameFROM GALLERY_galleries gINNER JOIN GALLERY_photos pON p.galleryID=g.galleryIDINNER JOIN (SELECT galleryID,MAX(PhotoID) AS LatestPhoto FROM GALLERY_photos GROUP BY galleryID)mON m.galleryID=g.galleryIDAND m.LatestPhoto=p.photoID[/code] |
 |
|
|
|
|
|
Return Gallery & Single Photo in Gallery