Getting the "latest" related record from a SQL DB

[William Blunn]

The solution looks sound.

But I take issue with your assertion that it "doesn't use a join".

Your query involves two tables, so there is a join.

Regards,

Bill

On 09/10/2014 17:57, Rob Lucas wrote:
> Something like this? Doesn't use a join etc, but has the merit of simplicity.
>
> SELECT * FROM Artist, Album WHERE Artist.Id = $artist_id AND Album.ArtistId = $artist_id ORDER BY album.date DESC LIMIT 1?
>
> R
>
> -----Original Message-----
> From: london.pm-bounces at london.pm.org [mailto:london.pm-bounces at london.pm.org] On Behalf Of Andrew Beverley
> Sent: 09 October 2014 13:29
> To: london.pm
> Subject: Getting the "latest" related record from a SQL DB
>
> 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
>
>
>