Selecting from four tables and grouping some results into one column-CodePudding

sports_games Table

==========================================================================
| game_id | team_id | game_type | time_started | time_ended | planned_by |
|========================================================================|
|   1     |    1    |     1     | 1640799417   | 1641146196 |     1      |
|   2     |    2    |     1     | 1640971535   | 1641579516 |     1      |
|   3     |    1    |     2     | 1640971553   | 1641582723 |     8      |
|   4     |    2    |     2     | 1640971585   | 1641404242 |     9      |
|   5     |    3    |     4     | 1641061431   | 1641754479 |     12     |
==========================================================================

I have been able to combine the above table, with the below table;

game_types Table

==============================
| game_type | game_type_name |
|============================|
|     1     |   football     |
|     2     |   baseball     |
|     3     |     golf       |
|     4     |    tennis      |
==============================

With the following SQL

SELECT *
    FROM sport_games
    INNER JOIN game_types
    ON sport_games.game_type = game_types.game_type

However, I am now finding myself going round and around, attempting potential solution after the other. I need to include data from two more tables, participents and user_basic.

The participents table simply has the game_id and user_id columns, one row per participant, both combined to be the primary key, and within user_basic table, you can find user_id along with their user_name.

For the planned_by column in the firt table, this is also a user_id which I want to obtain their username too.

With all the participants, I would like them to be within one column grouped by their game.

Things That I've Tried

group_concat( .. ), perhaps I was using this completely wrong, I could only achieve this grouping the entire results set from the other table and could not seem to figure out how to use this in a join correctly.
STRING_AGG( ... ) Initially this lead down a path of turning out that I could not use this as I was running on 5.5 MariaDB, which lead down struggling path for me to update. Now I'm using MariaDB 10.3, however I still cannot seem to get the simplest function to work for this, despite going through several Stack Overflows and whatnot.

I feel like I'm at a bit of a loss, any progress I seem to make forward, I am taking two steps back! And then there is using the JOIN amongst these, I'm not sure if I am wording my searches right to place me on the right path, so my question title might be a bit ambiguous right now, sorry if this is the case! - Please suggest otherwise, thank you.

Example Desired Outcome

"2": { // <- game_id
        "game_type": 2,
        "game_type_name": "baseball",
        "participants": {
            "1": "username_1",
            "2": "username_21",
            "3": "username_3",
            "4": "username_4",
        },
        "time_started": 1641061431,
        "time_completed": 1641754479,
        "planned_by": {
            "851730": "username_1",
        }
}

CodePudding user response：

You must join properly the tables and the table user_basic twice: to get the participants and the user who planned the game.

For MariaDB 10.5 you can use JSON_ARRAYAGG():

SELECT sg.game_id, 
       sg.game_type,
       gt.game_type_name,
       JSON_ARRAYAGG(JSON_OBJECT(ub.user_id, ub.user_name)) participants,
       sg.time_started,
       sg.time_ended,
       JSON_OBJECT(sg.planned_by, pb.user_name) planned_by
FROM sports_games sg
INNER JOIN user_basic pb ON pb.user_id = sg.planned_by
INNER JOIN game_types gt ON gt.game_type = sg.game_type
INNER JOIN participents p ON p.game_id = sg.game_id
INNER JOIN user_basic ub ON ub.user_id = p.user_id
GROUP BY sg.game_id;

For previous versions use GROUP_CONCAT():

SELECT sg.game_id, 
       sg.game_type,
       gt.game_type_name,
       CONCAT('{', GROUP_CONCAT(ub.user_id, ' : ', ub.user_name SEPARATOR ', '), '}') participants,
       sg.time_started,
       sg.time_ended,
       CONCAT('{', GROUP_CONCAT(DISTINCT sg.planned_by, ' : ', pb.user_name), '}') planned_by
FROM sports_games sg
INNER JOIN user_basic pb ON pb.user_id = sg.planned_by
INNER JOIN game_types gt ON gt.game_type = sg.game_type
INNER JOIN participents p ON p.game_id = sg.game_id
INNER JOIN user_basic ub ON ub.user_id = p.user_id
GROUP BY sg.game_id;

See the demo.

CodePudding user response：

Is time_ready an existing column or calculated from other columns? I don't see time_ready in any of the provided tables.

SELECT 
   game_types.game_type
   , game_types.game_type_name
   , participents.user_id
   , user_basic.user_name
   , sports_games.time_started
   , sports_games.time_ended AS time_completed
   , sports_games.planned_by
FROM sports_games
INNER JOIN game_types
   ON sports_games.game_type = game_types.game_type
INNER JOIN participents
   ON sports_games.game_id = participents.game_id
INNER JOIN user_basic
   ON participants.user_id = user_basic.user_id
ORDER BY
   game_types.game_type