Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004

    Unanswered: query to retrieve all albums a photo is in

    I could use a little help with a query for an online photo gallery system I built. I surmised from shopping around a little on the web that the way to create a photo gallery where photos can be organized into albums is to create 3 tables. Here is a simplified schema of what I have:

    photos (id, title, caption)
    albums (id, title)
    albumphotos (id, photo_id, album_id)

    This way, photos can belong to multiple albums, and I can easily retrieve all the photos in an album, etc. So far so good. Now, here comes my question:

    How do I construct a query that will return each photo and all of the albums that it is a member of?

    I'm thinking that I'd like a result that has columns like "photo title", "album1", "album2", "album3", etc. But I'd take the result any way I can get it. Please help if you can.

    Thanks very much in advance...

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    each photo and all of the albums that it is a member of:
    select p.title    as phototitle
         , p.caption
         , a.title    as albumtitle
      from photos as p
      join albumphotos as ap
        on = ap.photo_id
      join albums as a
        on ap.album_id
        by p.title  
         , a.title | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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