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.
Advertisement
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…