Grabbing the first image in an album

Databases
1

Hi

I have an Acccss Database which contains details about photos which belong to different albums. I’m trying to select the information about the album and grab the first photo that belongs to that album to use as a cover photo. If no photo has been added to the album I use a default image.

Tables

Albums
AlbumID PK
Description
DateModified

Photos
PhotoID PK
AlbumID FK
Filepath

This is the code I have so far, where am I going wrong?

SELECT 
Alb.AlbumID, 
Alb.[Description], 
Alb.[datemodified], 
IIF( Pho.FilePath IS NULL, 
'AwaitPhoto.jpg', 
Pho.FilePath ) AS FilePath
FROM Albums as Alb 
LEFT OUTER JOIN Photos as Pho  
ON (Pho.AlbumID = Alb.AlbumID  
AND Pho.PhotoID = 
 (SELECT MIN(PhotoID)   FROM Photos WHERE AlbumID = Alb.AlbumID )
)
2

Now it gives, Join expression not supported error!

Access drives me nuts.

PS: Appreciate your help

3

of course i knew it was Access SQL (ptui) – the square brackets are microsoft only, and SQL Server has no IIF function :slight_smile:

i just forgot one level of parentheses :rolleyes:

SELECT Alb.AlbumID
     , Alb.[Description]
     , Alb.[datemodified]
     , IIF( Pho.FilePath IS NULL
           , 'AwaitPhoto.jpg'
           , Pho.FilePath ) AS FilePath
  FROM [COLOR="Red"][B]([/B][/COLOR]
       Albums as Alb
LEFT OUTER 
  JOIN ( SELECT AlbumID
              , MIN(PhotoID) as FirstID
           FROM Photos
         GROUP
             BY AlbumID ) AS Fid
    ON Fid.AlbumID = Alb.AlbumID
       [COLOR="Red"][B])[/B][/COLOR]
LEFT OUTER 
  JOIN Photos as Pho
    ON (
       Pho.AlbumID = Alb.AlbumID
   AND Pho.PhotoID = Fid.FirstID
       )
4

Hi r937

On both your code and mine I get an error:

Syntax error(missing operator) in Query Expression ‘Fid.AlbumID = Alb.AlbumID LEFT OUTER JOIN Photos as Pho ON ( Pho.AlbumID = Alb.AlbumID AND Pho.PhotoID = Fid.FirstID’

Remember its Access SQL

5

um… remove the parentheses, remove one of the condition, then save it, open it up in design view, and graphically add back the second join condition (it will add parentheses automatically)

6

i can’t see that you’re going wrong at all

here’s a different way…


SELECT Alb.AlbumID
     , Alb.[Description]
     , Alb.[datemodified]
     , IIF( Pho.FilePath IS NULL
           , 'AwaitPhoto.jpg'
           , Pho.FilePath ) AS FilePath
  FROM Albums as Alb
LEFT OUTER 
  JOIN ( SELECT AlbumID
              , MIN(PhotoID) as FirstID
           FROM Photos
         GROUP
             BY AlbumID ) AS Fid
    ON Fid.AlbumID = Alb.AlbumID
LEFT OUTER 
  JOIN Photos as Pho
    ON (
       Pho.AlbumID = Alb.AlbumID
   AND Pho.PhotoID = Fid.FirstID
       )

p.s. nice job on returning a default image :slight_smile:

7

Well I currently have the SQL pasted into the Access Query Browser and Access won’t let me save it or switch views, it just gives the same error.

8

the “join expression not supported” refers to the two parenthesized conditions on the last LEFT OUTER JOIN

i think it goes away if you save the query and then run it

or open it up in design view

i forget the technique, but the sql is actually okay