Multiple Joins Duplicating Rows from Other Tables

I am trying to join multiple tables, however some rows are 'duplicating'. This is because one of the tables I am joining has more rows than the others (the 'Goals' table).

For exmaple, here are my three tables i am joining. The 'gamelog' (represents all the games played), 'teams' (representing all teams) and 'goals' (representing all goals that were scored in each game):

Gamelog

id	game_number	home_team_id	away_team_id
1	1	1	2
2	2	3	4

Teams

id	name
1	MTL
2	BOS
3	CGY
4	EDM

Goals

id	game_number_id	team_id_goal
1	1	2
2	1	2
3	1	1
4	2	4
5	2	4

When I join 'teams' and 'gamelog', it works great. I am able to display the team name instead of the id number. And it only displays one row per game. Here is my query prior to adding the third table 'goals':

Example A

$sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id, 
t1.name AS hometeam, t2.name AS awayteam";

FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id

$statement = $pdo->query($sql);
$results = $statement->fetchAll();

<?php foreach($results as $result) { ?>
  Game Number: <?=$result['game_number'];?>
   
    <?=$result['hometeam'].' VERSUS '.$result['awayteam']; ?>
    
    
    <?php } ?>

Result:

Game 1: MTL vs BOS

Game 2: EDM vs CGY

The problem is when I add the third table called 'goals'. As you can see there are three goals scored in game #1 between MTL (team_id = 1) and BOS(team_id = 2). And two goals scored in game #2 between CGY (team_id = 3) and EDM (team_id=4)/ When I join this, it duplicates everything in gamelog by amount of goals. So game #1 is duplicated x3, and game #2 is duplicated x2.

Here is the updated query with the third table. Changes are inside the double asterisks:

Example B

$sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id, 
t1.name AS hometeam, t2.name AS awayteam,
**goals.game_number_id**";

FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id
**JOIN goals ON gamelog.game_number = goals.game_number_id**

$statement = $pdo->query($sql);
$results = $statement->fetchAll();



 <?php foreach($results as $result) { ?>
        Game Number: <?=$result['game_number'];?>
        <br>
        <?=$result['hometeam'].' VERSUS '.$result['awayteam']; ?>
        <br>
        Which team scored the goals: <?=$result['team_id_goal']; ?>
        <br>
        
        <?php } ?>

The end result of this would have three rows under Game #1 like so:

game 1: MTL vs BOS

goals in game 1: MTL scores

goals in game 1: BOS scores

game 2: CGY vs EDM

game 2 CGY vs EDM

goals in game 2: EDM scores

I instead want it to look like this:

game 1: MTL vs BOS

goals in game 1: MTL scores

goals in game 1: BOS scores

game 2 CGY vs EDM

goals in game 2: EDM scores

How can I make this possible? I want to display all the goals scored in each game from 'goals' table without duplicating anything in 'gamelog' table.

Do i need to use a GROUP BY? I tried adding that into my code but my syntax must be off. I cannot get it work. Here is what I tried:

 $sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id, 
    t1.name AS hometeam, t2.name AS awayteam,
    goals.game_number_id,
    COUNT(*)";
    
FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id
JOIN goals ON gamelog.game_number = goals.game_number_id
GROUP BY gamelog.game_number

It may also have something to do with how my foreach loops are set up? Or maybe even the fact that I am using 'fetchAll'?

I even tried making two separate queries. One for the gamelog like in example A from above, and the other for the goals scored. The duplication stopped since my 'gamelog' table wasn't joined to the 'goals' table, but this just resulted in my php printing all the goals in database like so:

Game 1: MTL vs BOS

goals in game 1: MTL scores

goals in game 1: BOS scores

goals in game 2: EDM scores

Game 2: EDM vs CGY

goals in game 1: MTL scores

goals in game 1: BOS scores

goals in game 2: EDM scores

Really stumped by this. I feel like it is a small error on my end and would really appreciate any help with this :)

Edit: Here is a var_dump of my $results array. It is even showing columns i didnt even include in my SELECT query

array(8) { [0]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(1) } [1]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(1) } [2]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(2) } [3]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } [4]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } [5]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } [6]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(1) } [7]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(1) } }

Solution

It is the role of SQL to store and retrieve your data. It is the role of PHP to make that data meet your presentation requirements. Don't confuse the two roles and expect SQL to do presentation.

For SQL it is expected (and correct) that SQL will give you the multiplication of rows when the tables are joined, and that on each row you will see some data repeated (such as the game name). So, your problem isn't how the tables join, it is how do you use PHP to show only the items you want to see, in the order you want to see them.

See this operating at https://phpize.online/s/eC (using pdo)

<?php
// Using PDO
$sql = "SELECT g.game_number, ht.name AS home_team, at.name AS away_team
        FROM Gamelog g
        JOIN Teams ht ON g.home_team_id = ht.id
        JOIN Teams at ON g.away_team_id = at.id";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

foreach ($result as $row) {
    echo "game " . $row["game_number"] . ": " . $row["home_team"] . " vs " . $row["away_team"] . "\n";

    $sql2 = "SELECT t.name AS team_name
             FROM Goals g
             JOIN Teams t ON g.team_id_goal = t.id
             WHERE g.game_number_id = :game_number";
    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute(['game_number' => $row["game_number"]]);
    $result2 = $stmt2->fetchAll();

    foreach ($result2 as $row2) {
        echo "goals in game " . $row["game_number"] . ": " . $row2["team_name"] . " scores\n";
    }
}

result

game 1: MTL vs BOS
goals in game 1: BOS scores
goals in game 1: BOS scores
goals in game 1: MTL scores
game 2: CGY vs EDM
goals in game 2: EDM scores
goals in game 2: EDM scores

equivalent using MySQLi

// Using MySQLi
$sql = "SELECT g.game_number, ht.name AS home_team, at.name AS away_team
        FROM Gamelog g
        JOIN Teams ht ON g.home_team_id = ht.id
        JOIN Teams at ON g.away_team_id = at.id";
$result = $mysqli->query($sql);

while ($row = $result->fetch_assoc()) {
    echo "game " . $row["game_number"] . ": " . $row["home_team"] . " vs " . $row["away_team"] . "\n";

    $sql2 = "SELECT t.name AS team_name
             FROM Goals g
             JOIN Teams t ON g.team_id_goal = t.id
             WHERE g.game_number_id = ?";
    $stmt2 = $mysqli->prepare($sql2);
    $stmt2->bind_param("i", $row["game_number"]);
    $stmt2->execute();
    $result2 = $stmt2->get_result();

    while ($row2 = $result2->fetch_assoc()) {
        echo "goals in game " . $row["game_number"] . ": " . $row2["team_name"] . " scores\n";
    }
}
?>

https://phpize.online/s/GC