On Thu, 2014-10-09 at 13:28 +0100, Andrew Beverley wrote: > 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. > ... > 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.
Thanks for all the replies. Two good options from several people: limit the result to one row, or use a sub-select. What I failed to say was that as well as doing this for a single artist as per my question, I'd like to also be able to do it for several artists. Therefore (I think) I can't use either of those options without doing several database queries. I also like to avoid sub-selects as MySQL has a tendency to optimise them badly (as Gareth said). What I also failed to say was that I'm using DBIC and want to keep things database agnostic. So, I used Bill's suggestion, which seems to work well (http://london.pm.org/pipermail/london.pm/Week-of-Mon-20141006/025481.html) To get it to work with DBIC I had to create a custom join condition. Something like: $class->might_have( album_later => 'Album', sub { my $args = shift; return { "$args->{foreign_alias}.artist_id" => { -ident => "$args->{self_alias}.artist_id" }, "$args->{foreign_alias}.released" => { '>' => \"$args->{self_alias}.released" }, }; } ); And then add the relevant search criteria when using it: $search = { {'album' => 'album_later'}, ... } Thanks again for all the replies. Andy