Query to get most recent game results

Question

I keep track of certain game results in a MySQL database. Now I want to print the latest results in a nice HTML table. I have three tables:

Table persons contains all participants of the game.

+-----+---------+
| id  |  name   |
+-----+---------+
|  2  | Jon     |
|  3  | Philip  |
|  4  | Tom     |
|  5  | Joey    |
|  6  | Joanna  |
+-----+---------+

The table rounds contains information about each round of the game. Among other things, the week in which the game was fought.

+-----+------+
| id  | week |
+-----+------+
|  1  |    9 |
|  2  |   10 |
|  3  |   11 |
|  4  |   12 |
|  5  |   13 |
+-----+------+

And the table results contains the results for each person and round. The result column is a score in the game.

+------------+----------+--------+
|  personId  | roundId  | result |
+------------+----------+--------+
|         2  |       1  |      2 |
|         4  |       1  |      6 |
|         5  |       1  |      6 |
|         3  |       1  |     10 |
|         2  |       2  |     16 |
|         4  |       2  |     14 |
|         5  |       2  |      5 |
|         3  |       2  |     11 |
+------------+----------+--------+

Now I want to print a table with the players scores each week. I want my output to look like the table below. Note that if a player did not participate one week, the cell should be empty.

+------+-----+--------+-----+------+--------+
| Week | Jon | Philip | Tom | Joey | Joanna |
+------+-----+--------+-----+------+--------+
|    9 |   2 |     10 |   6 |    6 |        |
|   10 |  16 |     11 |  14 |    5 |        |
+------+-----+--------+-----+------+--------+

So my question is: How do I do to get such output?

This is not a duplicate. See comments below.

you also need to add weekID into result table, so you can easily link result with each week — calvix, Apr 13 '16 at 12:14
You want a PIVOT and there's no PIVOT function in MySQL, however it is possible to mimick it quite easily, if your number of players is low and fixed. Otherwhise you will have to create a dynamic query using a procedure. So how many players do you have and is it an option to hardcode the players in a query ? — Thomas G, Apr 13 '16 at 12:29
@ThomasG The number of player is low and fixed indeed. It is only the participants listed in the question, no more. — Emil, Apr 13 '16 at 12:54
Unfortunately I can't answer anymore as the question was marked as duplicate. Allthough @fancyPants I don't exactly agree about it being duplicate with that precise duplicate question because following your last comment you don't really need a dynamic query — Thomas G, Apr 13 '16 at 13:03

Thomas G · Accepted Answer · 2016-04-13T19:36:39.020

So as stated in comments, you want to make a PIVOT, but MySQL does not support it.

However since your number of players is low and fixed, you can hardcode the players in a GROUP BY query like this :

SELECT  R.Week, 
        SUM(CASE WHEN  P.name = 'Jon'       THEN S.result END) AS Jon,
        SUM(CASE WHEN  P.name = 'Philip'    THEN S.result END) AS Philip,
        SUM(CASE WHEN  P.name = 'Tom'       THEN S.result END) AS Tom,
        SUM(CASE WHEN  P.name = 'Joey'      THEN S.result END) AS Joey, 
        SUM(CASE WHEN  P.name = 'Joanna'    THEN S.result END) AS Joanna                                                     
FROM persons P 
    LEFT JOIN  results S ON P.id=S.personId
    LEFT JOIN rounds R ON R.id=S.roundId
WHERE R.week IS NOT NULL
GROUP BY R.Week

SqlFiddleDemo

Query to get most recent game results

1 Answers1