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

Question

I am trying to show some simple results of a computer game and make it easier to repeat the results line by line in my code. I want all the relevant data for each game to be in each record, so I can print it all on one line, for example:

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

So, for the game there are two teams that play with each other, and each of them receives a score at the end of the game. Essentially, each game table has two rows in the games_teams table.

Here is my diagram:

Here is my table data:

Here's the result I'm trying to achieve, so I can easily iterate over the results and display them on the page:

I managed to achieve this with some horrible SQL and lots of subqueries like:

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 

The problem with this method will be slow and it does not scale. I also need to pull other game statistics from the games_teams table so that there are even more subqueries.

Another method I tried was:

I achieved this 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 method will be more difficult to execute in the code, since the corresponding data for each game will be in two different records. I would have to build the first part of the line, and then move on to the next iteration of the loop and print the next part. Then run it again for the next game, I try to display all the information on one line, for example:

Team A (score 45) versus Team B (score 55), game duration: 5 minutes

So why do I think it would be easier if everything was on one record. Is there a way to do this nicely, and so it scales 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 is much appreciated, thanks!

Answer 1

You will need to join games_teams and teams twice, for example:

 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 compress the subtitle {games_teams * teams} and refer to it twice by putting it in the CTE: (unfortunately mysql does not support CTE)

 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 due to the fact that each game always has exactly two teams, so you can use UNION to get commands for each game, for example:

 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;