3

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.

Improve this question

1 Answer 1

Reset to default
4

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
Improve this answer
3
  • There is table bunches. I just haven't said it explicitly
    – porton
    Commented Mar 6, 2013 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
    Commented Mar 6, 2013 at 20:55
  • What about just select p.image from photos p natural join albums where bunchid = 1?
    – Wildcard
    Commented Aug 24, 2017 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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