Fetch last x records for every id in list

Question

So I have the following 4 tables (unnecessary columns removed):

match_player:
+----------+-----------+-----------+-------+------+
| match_id | metric_id | player_id | team  | elo  |
+----------+-----------+-----------+-------+------+
|        1 |       100 |         1 | Alpha | 1140 |
|        2 |       101 |         3 | Beta  | 1140 |
|        3 |       102 |         2 | Beta  | 1200 |
|        3 |       103 |         3 | Alpha | 1132 |
+----------+-----------+-----------+-------+------+

match:
+----------+---------------------+---------------------+------+--------+-------------+------------+
| match_id | start               | end                 | mode | ranked | goals_alpha | goals_beta |
+----------+---------------------+---------------------+------+--------+-------------+------------+
|        1 | 2016-03-21 04:07:41 | 2016-03-21 04:25:26 | 5v5  |      1 |           4 |          6 |
|        2 | 2016-03-21 03:48:03 | 2016-03-21 04:06:41 | 5v5  |      0 |           5 |         10 |
|        3 | 2016-03-21 03:06:37 | 2016-03-21 03:31:06 | 5v5  |      1 |           7 |         13 |
+----------+---------------------+---------------------+------+--------+-------------+------------+

player:
+-----------+----------+
| player_id | name     |
+-----------+----------+
|         1 | Player 1 |
|         2 | Player 2 |
|         3 | Player 3 |
+-----------+----------+

metric:
+-----------+--------+-------+
| metric_id | passes | goals |
+-----------+--------+-------+
|       100 |     10 |     1 |
|       101 |     12 |     0 |
|       102 |      4 |     5 |
|       103 |      9 |     3 |
+-----------+--------+-------+

Now, as the title suggests, I would like to retrieve the last x matches for each player who's player_id is in a list retrieved by a previous query. I know how to get 1 player's last x matches, but for the life of me, I can't figure out how to get the matches for more than 1 player (of course I could use UNION to combine the results of each query for each single player, but I doubt that's efficient when dealing with 100+ players).

It would be great if a "simple" solution were found. If not, I guess I'll have to create another table last_30_matches or so and have that be updated when a match is added to the match table.

Thanks in advance to everybody who takes their time to look into this.

EDIT:

Query to select last x matches for 1 player:

SELECT
  *
FROM
  `match`
JOIN
  match_player ON match_player.match_id = `match`.`match_id`
JOIN
  metric ON metric.metric_id = match_player.metric_id
JOIN
  player ON player.player_id = match_player.player_id
WHERE
  player.player_id = <id>
ORDER BY
  `match`.end DESC
LIMIT x

http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Mihai, Mar 22 '16 at 13:17
Please post your current query that fetches it for 1 player. — sagi, Mar 22 '16 at 13:19
I did, but then applying the `LIMIT x` limits the **total** amount of returned matches, not the amount of matches **per player** — Chris Satchell, Mar 22 '16 at 13:37
Well, the easiest way to do that is if you're using some code (like php, c#, etc.) then iterate though the players and query it separately, or you probably can do that in a mysql function too. — Derenir, Mar 22 '16 at 15:03

Fetch last x records for every id in list

0 Answers0