SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grabbing the first image in an album.

    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?

    Code:
    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 )
    )
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i can't see that you're going wrong at all

    here's a different way...
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    of course i knew it was Access SQL (ptui) -- the square brackets are microsoft only, and SQL Server has no IIF function

    i just forgot one level of parentheses
    Code:
    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
           )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now it gives, Join expression not supported error!

    Access drives me nuts.

    PS: Appreciate your help
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •