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 seq
s of the albums and then by IDs of photos in the album.
Best Answer
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: