Why not include a sub-select like, ... where album.published = (select max(album.published) from album join artist on album.artist_id = artist.id) ...
Paul On Thu, Oct 9, 2014 at 5:28 AM, Andrew Beverley <a...@andybev.com> wrote: > Hi guys, > > I'm after some best-practice advice regarding SQL database design. > > I have a table (say "artist", couldn't resist...) that has a one-to-many > relationship to another table (say "album"). The album table has a field > which references the artist table's ID. So one artist can have many > albums. > > So, if I want to know all of an artist's albums, that's easy. > > But what if I want to fetch an artist's details and his latest album? I > can select the artist from the artists table and then join the albums > table. But to get the latest album I'd have to use a max function (say > on the album's date), with which it isn't possible to get the related > fields in the same row. > > I see 2 ways of solving this: > > - Run multiple queries to get the relevant album's ID (if even possible) > and then retrieve its row in entirety. > > - Have a reference from the artist table back to the album table, > specifying which is the latest album, which I update each time the > albums table is updated. > > Neither seem particularly tidy to me, so am I missing something > completely obvious? > > Thanks, > > Andy > >