Mysql – Using an IF statement for all rows in a query

mariadbMySQL

I'm working on a soccer database right now, and I am trying to solve one little problem.

So I have the table games, where I stored home team, away team, miscstats and boxscore:

game_id | hometeam_id | awayteam_id | miscstats_id | boxscore_id
1         2             4             1              1
2         3             7             2              2
3         4             3             3              3
4         8             5             4              4

In boxscores I store one row per player and match:

boxscore_id | player_id | team_id | goals | fouls
1             1           2         1       4
1             2           2         0       3
1             3           4         1       7
1             4           4         1       10
2             5           3         5       0

And in miscstats I store random stats, like number of yellow cards showed to the bench players:

miscstats_id | homebenchyellowcards | awaybenchyellowcards
1              3                      4
2              1                      5
3              2                      6
4              7                      10

Now, for some advanced statistics calculation, I would like to aggregate the number of homebenchyellowcards and awaybenchyellowcards for each team. So, for example, if my select function is correct, team number 3 has 7 bench yellow cards, 1 from game 2 (when team played at home) and 6 from game 3 (when team played away).

I am trying to do this:

SELECT (IF (e.hometeam_id=b.team_id, x.homebenchyellowcards, x.awaybenchyellowcards)) as 
bench_yellow_cards FROM miscstats as x INNER JOIN games as e ON (e.miscstats_id=x.miscstats_id)
INNER JOIN boxscores as b ON (e.boxscore_id=b.boxscore_id) WHERE b.team_id = 2

But it gives me '1' as a result. I have been reading that this is because the 'if' function just analyses the first row when this condition is met (so, game_id 2) and then stops with the rest of the rows. I tried SUM statements too, but then it sums every row in homebenchyellowcards.

Anybody has an idea on how passing the if condition to all the rows in order to sum them properly?

Thanks in advance!

Best Answer

Your description looks messy. Expression from your SELECT statement will be evaluated for each row in received result set. I didn't understand why do you need to join boxscores table. Actually your original query should return more than one row. If you want to find the number of yellowcards for team_id = 2 you can use such query:

SELECT SUM(IF(e.hometeam_id = 2, x.homebenchyellowcards, x.awaybenchyellowcards))
FROM games AS e
JOIN miscstats AS x ON e.miscstats_id = x.miscstats_id 
WHERE e.hometeam_id = 2 OR e.awayteam_id = 2;
Related Question