How to join multiple tables including lookup table and return data in rows

Question

I'm trying to display some simple computer game results and make it easy to iterate through the results line by line in my code. I want it so that all the relevant data for each game is in each record so I can output it all on the one line e.g.:

• Team A (score 45) vs. Team B (score 55), game duration: 5 mins
• Team C (score 60) vs. Team D (score 65), game duration: 4.3 mins

So for a game there is two teams that play each other and they each get a score at the end of the game. Essentially there ends up being two rows in the games_teams table for every game.

Here's my schema:

Here's my table data:

Here's the output I'm trying to achieve so I can easily iterate through the results and output them on the page:

I managed to achieve that with some horrific SQL and lots of subqueries like so:

SELECT games.game_id, game_name, game_duration, 
(SELECT team_id FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 0, 1) AS team_id_a,
(SELECT team_id FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 1, 1) AS team_id_b,
(SELECT teams.team_name FROM games_teams INNER JOIN teams ON games_teams.team_id = teams.team_id WHERE games.game_id = game_id LIMIT 0, 1) AS team_name_a,
(SELECT teams.team_name FROM games_teams INNER JOIN teams ON games_teams.team_id = teams.team_id WHERE games.game_id = game_id LIMIT 1, 1) AS team_name_b,
(SELECT team_score FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 0, 1) AS team_score_a,
(SELECT team_score FROM games_teams WHERE games.game_id = games_teams.game_id LIMIT 1, 1) AS team_score_b
FROM games

Problem with that method is it'll be slow and it doesn't scale. I also need to pull out other game stats from the games_teams table so that'll be even more subqueries.

The other method I tried was:

I achieved that with the following SQL:

SELECT games.game_id, game_name, game_duration, teams.team_id, team_name, team_score
FROM games
INNER JOIN games_teams ON games.game_id = games_teams.game_id
INNER JOIN teams ON games_teams.team_id = teams.team_id

Now this way will be harder to foreach through in the code as the relevant data for each game is in two different records. I'd have to build the first part of the row, then go into the next loop iteration and print the next part. Then start it all over again for the next game, I'm trying to display all the information on one line like:

Team A (score 45) vs Team B (score 55), game duration: 5mins

So that's why I think it would be easier if it was all on the one record. Is there a way to accomplish this nicely and so it scales as well if I need more columns in the games_teams table?

Here's a pastebin link with the database code if you need to recreate it.

Any help much appreciated, thanks!

Answer 1

You'll need to join the games_teams and teams twice, like:

SELECT ga.game_id
        , ga.game_name
        , ga.game_duration
        , t1.team_name, gt1.team_score
        , t2.team_name, gt2.team_score
FROM games ga
JOIN games_teams  gt1 ON gt1.game_id = ga.game_id
JOIN games_teams  gt2 ON gt2.game_id = ga.game_id
JOIN teams t1 ON t1.team_id = gt1.team_id
JOIN teams t2 ON t2.team_id = gt2.team_id
WHERE gt1.team_id < gt2.team_id
        ;

A clean way to do squeeze out the {games_teams * teams} sub-join and refer to it twice is by putting it into a CTE: (unfortunately mysql does not support CTEs)

WITH gtx AS (
        SELECT gt.game_id
        , gt.team_score
        , te.team_id
        , te.team_name
        FROM games_teams gt
        JOIN teams te ON te.team_id = gt.team_id
        )
SELECT ga.game_id 
        , ga.game_name
        , ga.game_duration
        , g1.team_name, g1.team_score
        , g2.team_name, g2.team_score
FROM games ga
JOIN gtx g1 ON g1.game_id = ga.game_id
JOIN gtx g2 ON g2.game_id = ga.game_id
WHERE g1.team_id < g2.team_id
  ;

Result:

 game_id | game_name | game_duration | team_name | team_score | team_name | team_score 
---------+-----------+---------------+-----------+------------+-----------+------------
       1 | Game A    |           300 | Team A    |         45 | Team B    |         55
       2 | Game B    |           258 | Team C    |         60 | Team D    |         65
(2 rows)

Answer 2

This is simplified by the fact that there are always exactly two teams per game so that you can use a UNION to get the teams for each game, like so:

SELECT q2.game_id, games.game_name, games.game_duration,
       q2.team_id_a, q2.team_id_b,
       teams_a.team_name AS team_name_a,
       teams_b.team_name AS team_name_b,
       games_teams_a.team_score AS team_score_a,
       games_teams_b.team_score AS team_score_b
FROM  (SELECT q.game_id,
              MAX(q.team_id_a) AS team_id_a,
              MAX(q.team_id_b) AS team_id_b
          FROM (
               SELECT games_teams.game_id,
                      MIN(games_teams.team_id) AS team_id_a,
                      0 AS team_id_b
               FROM   games_teams
               GROUP BY games_teams.game_id
            UNION
               SELECT games_teams.game_id,
                      0 AS team_id_a,
                      MAX(games_teams.team_id) AS team_id_b
               FROM   games_teams
               GROUP BY games_teams.game_id
         ) q
        GROUP BY q.game_id) q2
   INNER JOIN games ON q2.game_id = games.game_id
   INNER JOIN teams teams_a ON q2.team_id_a = teams_a.team_id
   INNER JOIN teams teams_b ON q2.team_id_b = teams_b.team_id
   INNER JOIN games_teams games_teams_a ON
              q2.game_id = games_teams_a.game_id AND q2.team_id_a = games_teams_a.team_id
   INNER JOIN games_teams games_teams_b ON
              q2.game_id = games_teams_b.game_id AND q2.team_id_b = games_teams_b.team_id;