Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Table albums has (among other field) field id.

Table photos has column id, field album which is a foreign key referring to album id and some other fields (which are irrelevant for the question I ask).

So in an album there is several photos (photos in an album are ordered by id of a photo).

Now I introduce one more "level of indirection": Bunches. There may be several albums in one bunch.

For this I add fields bunch and seq INT UNSIGNED into the table albums. bunch is the ID of the bunch where the album belongs and seq is the number of the album in the bunch.

Now the problem:

Let it is given a bunch ID.

I want to make a SELECT query which selects all photos from albums belonging to the given bunch, ordered first by seqs of the albums and then by IDs of photos in the album.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

I'm not sure why bunches wouldn't be its own table (I think that would make this problem easier), but I'll run with it as is. If I'm understanding the problem correctly, assuming you wanted bunch 1, something like this should do the trick:

SELECT p.image
FROM photos p
  JOIN album a
    ON p.album = a.id
WHERE a.bunch = 1
ORDER BY a.id, a.seq, p.id
share|improve this answer
    
There is table bunches. I just haven't said it explicitly –  porton Mar 6 '13 at 20:40
    
Gotcha, this will work as is with the design (it worked on a test db I made based on my understanding from your problem). Personally though I would use linking tables, unless an album can only belong to one and only one bunch and a photo to one and only one album. –  Dan Mar 6 '13 at 20:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.