Multiple rows in left join causing duplicate data

I have a rather complex MySQL query designed to retrieve teams and their league performance – from the following tables:

teams

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name             | varchar(80)      | NO   | UNI | NULL    |                |
| primary_colour   | varchar(30)      | YES  |     | NULL    |                |
| secondary_colour | varchar(30)      | YES  |     | NULL    |                |
| tertiary_colour  | varchar(30)      | YES  |     | NULL    |                |
| logo_url         | text             | YES  |     | NULL    |                |
| narrative        | text             | YES  |     | NULL    |                |
| club_id          | int(10) unsigned | YES  | MUL | NULL    |                |
| created_at       | timestamp        | YES  |     | NULL    |                |
| updated_at       | timestamp        | YES  |     | NULL    |                |
| deleted_at       | timestamp        | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

division_season_team

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| division_id | int(10) unsigned | YES  | MUL | NULL    |                |
| season_id   | int(10) unsigned | YES  | MUL | NULL    |                |
| team_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| created_at  | timestamp        | YES  |     | NULL    |                |
| updated_at  | timestamp        | YES  |     | NULL    |                |
| deleted_at  | timestamp        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

team_point_adjustments

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| point_adjustment | int(11)          | NO   |     | 0       |                |
| reason           | varchar(191)     | NO   |     | NULL    |                |
| reason_date      | date             | NO   |     | NULL    |                |
| team_id          | int(10) unsigned | YES  | MUL | NULL    |                |
| season_id        | int(10) unsigned | YES  | MUL | NULL    |                |
| created_at       | timestamp        | YES  |     | NULL    |                |
| updated_at       | timestamp        | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

matches

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| home_score  | int(11)          | NO   |     | 0       |                |
| away_score  | int(11)          | NO   |     | 0       |                |
| division_id | int(10) unsigned | YES  | MUL | NULL    |                |
| season_id   | int(10) unsigned | YES  | MUL | NULL    |                |
| home_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| away_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| match_date  | datetime         | NO   |     | NULL    |                |
| court       | int(10) unsigned | NO   |     | NULL    |                |
| round       | int(10) unsigned | NO   |     | NULL    |                |
| played      | tinyint(1)       | NO   |     | 0       |                |
| walkover    | tinyint(1)       | NO   |     | 0       |                |
| home_adjust | int(11)          | NO   |     | 0       |                |
| away_adjust | int(11)          | NO   |     | 0       |                |
| created_at  | timestamp        | YES  |     | NULL    |                |
| updated_at  | timestamp        | YES  |     | NULL    |                |
| deleted_at  | timestamp        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Here’s the query:

"SELECT 
        t.name AS team_name,
        t.id as team_id,
        SUM(win) AS win,
        SUM(draw) AS draw,
        SUM(loss) AS loss,
        SUM(goals_for) AS goals_for,
        SUM(goals_against) AS goals_against,
        SUM(goal_difference) as goal_difference,
        SUM(games_played) AS games_played,
        SUM(points) + IFNULL(tpa.point_adjustment, 0) as points
        FROM teams t 
        LEFT JOIN team_point_adjustments tpa
            ON t.id = tpa.team_id
            AND (tpa.season_id = 1 OR tpa.season_id IS NULL)
        INNER JOIN
            division_season_team dst
            ON t.id = dst.team_id
        LEFT JOIN (
            SELECT home_id
                team_name,
                IF(home_SCORE > away_score, 1,0) win,
                IF(home_score = away_score, 1,0) draw,
                IF(home_score < away_score, 1,0) loss,
                home_score goals_for,
                away_score goals_against,
                home_score - away_score goal_difference,
                1 games_played,
                CASE WHEN home_score > away_score THEN 5 WHEN home_score = away_score THEN 2 ELSE 0 END points
            FROM matches mat WHERE played = 1 AND season_id = 1 AND division_id = 1
            UNION ALL 
            SELECT away_id,
                IF(home_score < away_score, 1, 0),
                IF(home_score = away_score, 1,0),
                IF(home_score > away_score, 1,0),
                away_score,
                home_score,
                away_score - home_score goal_difference,
                1 games_played,
                CASE WHEN home_score < away_score THEN 5 WHEN home_score = away_score THEN 2 ELSE 0 END
            FROM matches
            WHERE played = 1 AND season_id = 1 AND division_id = 1
            ) AS total ON total.team_name=t.id WHERE dst.season_id = 1 AND dst.division_id = 1
        GROUP BY t.id
        ORDER BY points DESC, goal_difference DESC"

Which results in the following, as an example:

{
    "team_name": "Jets",
    "team_id": 20,
    "win": "8",
    "draw": "0",
    "loss": "4",
    "goals_for": "354",
    "goals_against": "265",
    "goal_difference": "89",
    "games_played": "12",
    "points": 40
}

The issue is with the first left join on team_point_adjustments. If 2 rows match, ie there are 2 rows corresponding to a team, the data returned is doubled – number of games played, wins, points etc are all double what they should be.

Have tried adding a group by clause for this sub query, but get a syntax error. Data is all correct if there is only one matching row in this left join.

Answer

Aggregate on the table matches and the table division_season_team before the joins which both must be LEFT joins just in case:

SELECT 
        t.name AS team_name,
        t.id as team_id,
        total.win,
        total.draw,
        total.loss,
        total.goals_for,
        total.goals_against,
        total.goal_difference,
        total.games_played,
        total.points + IFNULL(tpa.point_adjustment, 0) as points
FROM teams t 
LEFT JOIN (
  SELECT team_id, season_id, SUM(point_adjustment) AS point_adjustment
  FROM team_point_adjustments
  GROUP BY team_id, season_id
) tpa
ON t.id = tpa.team_id AND (tpa.season_id = '" . $season_id . "' OR tpa.season_id IS NULL)
LEFT JOIN division_season_team dst ON t.id = dst.team_id
LEFT JOIN (
          SELECT
            g.team_id,
            g.team_name,
            SUM(g.win) AS win,
            SUM(g.draw) AS draw,
            SUM(g.loss) AS loss,
            SUM(g.goals_for) AS goals_for,
            SUM(g.goals_against) AS goals_against,
            SUM(g.goal_difference) as goal_difference,
            SUM(g.g.games_played) AS games_played,
            SUM(points) AS points
          FROM (
            SELECT home_id
                team_name,
                IF(home_SCORE > away_score, 1,0) win,
                IF(home_score = away_score, 1,0) draw,
                IF(home_score < away_score, 1,0) loss,
                home_score goals_for,
                away_score goals_against,
                home_score - away_score goal_difference,
                1 games_played,
                CASE WHEN home_score > away_score THEN '" . $win_value . "' WHEN home_score = away_score THEN '" . $draw_value . "' ELSE '" . $loss_value . "' END points
            FROM matches mat WHERE played = 1 AND season_id = '" . $season_id . "' AND division_id = '" . $division_id . "'
            UNION ALL 
            SELECT away_id,
                IF(home_score < away_score, 1, 0),
                IF(home_score = away_score, 1,0),
                IF(home_score > away_score, 1,0),
                away_score,
                home_score,
                away_score - home_score goal_difference,
                1 games_played,
                CASE WHEN home_score < away_score THEN '" . $win_value . "' WHEN home_score = away_score THEN '" . $draw_value . "' ELSE '" . $loss_value . "' END
            FROM matches
            WHERE played = 1 AND season_id = '" . $season_id . "' AND division_id = '" . $division_id . "'
          ) g
          GROUP BY g.team_id, f.team_name       
) AS total ON total.team_id=t.id 
WHERE dst.season_id = '" . $season_id . "' AND dst.division_id = '" . $division_id . "'
ORDER BY points DESC, goal_difference DESC

I hope I have no typos…