Query to show match information for last 2 rounds of tournament

Question

I dont know how to achieve this.

I have 2 tables in my database:

Table 1: rounds

+------------+----------+---------+----------+---------------+
|  round_id  |  format  |  Size   |  poster  | tournament_id |
+------------+----------+---------+----------+---------------+
|     6      |  Single  |   12    |  Luis    |      1
|     7      |  Single  |   9     |  Daniel  |      1        |
|     8      |  Single  |   4     |  Oscar   |      1        |
+------------+----------+---------+----------+---------------+

Table 2: matches

+------------+----------+-------------+----------+----------+
|  round_id  |  player  |  position   |  score   |  winner  |
+------------+----------+-------------+----------+----------+
|     6      |  Doris   |     0       |    1     |    1     |
|     7      |  Arturo  |     0       |    3     |    1     |
|     7      |  Pablo   |     1       |    2     |    0     |
|     8      |  Ale     |     0       |    1     |    0     |
|     8      |  Maria   |     1       |    5     |    1     |
|     8      |  Roberto |     2       |    4     |    0     |
+------------+----------+-------------+----------+----------+

At the first table i have the rounds of the tournaments and at the second table the matches from each round, as you can see each match in a round can have different size of players. So i want to get all info of the rounds BUT just the last 2 rounds from the table "rounds", to get something like this in one query:

Result from query:

//The main key can be the round_id or numbers in order 0 to n //

[7] => Array
    (
        ['poster'] => 'Daniel'
        ['format'] => 'single'

            (
                [0] => Array
                      (
                          ['player'] => 'Arturo'
                          ['score'] => 3
                       )
                [1] => Array
                       (
                          ['player'] => 'Pablo'
                          ['score'] => 2
                       )
            )

    )
[8] => Array
    (
        ['poster'] => 'Oscar'
        ['format'] => 'single'

            (
                [0] => Array
                      (
                          ['player'] => 'Ale'
                          ['score'] => 1
                       )
                [1] => Array
                       (
                          ['player'] => 'Maria'
                          ['score'] => 5
                       )
                [2] => Array
                       (
                          ['player'] => 'Roberto'
                          ['score'] => 4
                       )
            )

    )

So in my page i can show the last rounds uploaded, something like this:

+---------------------------+
|  by Daniel                |
|  Arturo (3) vs Pablo (2)  |
+---------------------------+

+---------------------------------------+      
|  by Oscar                             |
|  Ale (1) vs Maria (5) vs Roberto (4)  |
+---------------------------------------+

Hope a MySQL expert can help me with this :)

do they all belong to the same tournament? does this matter? — Aleksander Rezen, Jun 07 '17 at 21:53
i just want to show the last rounds uploaded with the information. doesn´t matter if its from different tournament — Rob, Jun 07 '17 at 22:30

Barmar · Accepted Answer · 2017-06-07T23:35:23.297

1

Join the matches table with a subquery that gets the last 2 rows in the rounds table.

SELECT *
FROM (SELECT *
      FROM rounds
      ORDER BY round_id DESC
      LIMIT 2) AS r
JOIN matches AS m ON r.round_id = m.round_id

See Creating one array from another array in php for how you can combine the match information for the same round in the PHP array.

If you want the query to return one row for each round, you can use GROUP_CONCAT to combine the match information into a single column:

SELECT r.poster, r.format, GROUP_CONCAT(CONCAT(m.player, '(', m.score, ')') SEPARATOR ' vs ') AS players
FROM (SELECT round_id, poster, format
      FROM rounds
      ORDER BY round_id DESC
      LIMIT 2) AS r
JOIN matches AS m ON r.round_id = m.round_id
GROUP BY m.round_id

edited Jun 07 '17 at 23:35

answered Jun 07 '17 at 21:54

Barmar

741,623
53
500
612

Thanks! but with your query i get differents arrays for each player, and the rounds info repeat in this arrays :( – Rob Jun 07 '17 at 22:26
You combine the rows for the same round in the PHP loop. The question I linked to shows how to do that. – Barmar Jun 07 '17 at 23:12
Yeah i know, i fact i could do that but i want an optmize way to do it. Because here i just show an example and the "rounds" table have more columns actually. At least not repeat the round information in each array? – Rob Jun 07 '17 at 23:29
I've added a version that uses `GROUP_CONCAT` to return the output in the format you showed. – Barmar Jun 07 '17 at 23:35

Query to show match information for last 2 rounds of tournament

1 Answers1