I'm looking to see if I can get the results I need with a single query, and my MySQL skills are still in their adolescence over here.
I have 4 tables: shows
, artists
, venues
and tours
. A simplified version of my main query right now looks like this:
SELECT *
FROM artists AS a,
venues AS v,
shows AS s
LEFT JOIN tours AS t ON s.show_tour_id = t.tour_id
WHERE s.show_artist_id = a.artist_id
AND s.show_venue_id = v.venue_id
ORDER BY a.artist_name ASC, s.show_date ASC;
What I want to add is a limit on how many shows are returned per artist. I know I could SELECT * FROM artists
, and then run a query with a simple LIMIT
clause for each returned row, but I figure there must be a more efficient way.
UPDATE: to put this more simply, I want to select up to 5 shows for each artist. I know I could do this (stripping away all irrelevancies):
<?php
$artists = $db->query("SELECT * FROM artists");
foreach($artists as $artist) {
$db->query("SELECT * FROM shows WHERE show_artist_id = $artist->artist_id LIMIT 5");
}
?>
But it seems wrong to be putting another query within a foreach
loop. I'm looking for a way to achieve this within one result set.