Sequential joining of tables in order

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.

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

StackExchange Database Administrators Q#36101, answer score: 4