query to retrieve all albums a photo is in

one09jason · #1 (**permalink**) 09-09-04, 19:12

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...

r937 · #2 (**permalink**) 09-09-04, 20:06

each photo and all of the albums that it is a member of:

Code:

select p.title    as phototitle
     , p.caption
     , a.title    as albumtitle
  from photos as p
inner
  join albumphotos as ap
    on p.id = ap.photo_id
inner
  join albums as a
    on ap.album_id
     = a.id 
order
    by p.title  
     , a.title

Internet Services

Web Development

Digital Marketing

Consumer Tech