Limiting MySQL results in a query

Question

I want to see if I can get the results I need with one query, and my MySQL skills are still in my teens.

I have 4 tables: shows, artists, venuesand tours. A simplified version of my main request 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 the number of impressions that are shown for each artist . I know I can SELECT * FROM artists, and then run a query with a simple sentence LIMITfor each row returned, but I believe there should be a more efficient way.

UPDATE: to make this easier, I want to select up to 5 impressions for each artist. I know that I can do this (remove all inconsistencies):

<?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 put another request in a loop foreach. I am looking for a way to achieve this in one set of results.

Answer 1

These are the stored procedures used for.

Select a list of artists, then scroll through the list by adding 5 or less impressions for each artist to the temp table.

Then return the temp table.

Answer 2

-B, SQL, (, ..) . , , . , .

Answer 3

, , , , (. ...), , . , 5 ... , .

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
  AND s.show_id IN
     (SELECT subS.show_id FROM shows subS 
      WHERE subS.show_artist_id = s.show_artist_id
      ORDER BY subS.show_date DESC
      LIMIT 5)
ORDER BY a.artist_name ASC, s.show_date ASC;