Getting the "latest" related record from a SQL DB
david at cantrell.org.uk
Fri Oct 10 13:40:22 BST 2014
On Thu, Oct 09, 2014 at 01:28:44PM +0100, Andrew Beverley wrote:
> 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 can have more than one artist, of course ...
> 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'm sure it must be possible with a HAVING and GROUP BY, but I'm
buggered if I can get it to work.
More information about the london.pm