Maybe this question is super specific but I can't really find a way to do it. A bit of background. I'm studying, and In order to learn this I'm playing around counting stats from our Wednesday football matches with my friends.

Now in order to make data as normalized as possible and calculate everything programmatically, I have a table called matches and a table called goals.

Matches

 ---- ------------ 
| id | date       |
 ---- ------------ 
| 1  | 05-01-2022 |
 ---- ------------ 
| 2  | 12-01-2022 |
 ---- ------------ 

Goals

 ---- -------------- 
| id | match | team |
 ---- -------------- 
| 1  |   1   |  A   |
 ---- -------------- 
| 2  |   1   |  B   |
 ---- -------------- 
| 3  |   1   |  B   |
 ---- -------------- 
| 4  |   2   |  A   |
 ---- -------------- 
| 5  |   2   |  A   |
 ---- -------------- 

So my objective here is to count the goals for each team in each match, compare both values to determine the winner and come up with a result like this:

Results

 ------- ------------ 
| match | result     |
 ------- ------------ 
| 1     | B          |
 ------- ------------ 
| 2     | A          |
 ------- ------------ 

I'm currently doing this fairly straightforward using laravel on my app, but I'm trying to get it done through SQL queries only.

I also know that this would be much easier, if i simply added a goals_team_a, goals_team_b, result columns to my Matches table. If this is the actual correct solution is super easy to add, I just though at some point goals may differ from the score because of a typo or something and this is the way to be extra sure. Probably an overkill

CodePudding user response：

The current structure of matches and goals is definitely flawed. If a given match id has no goals scored by either side there is no record of who played. By simply adding home and away teams to the match you have a more meaningful match entity and it becomes simple to query for the result.

Matches

Teams

Additionally, if your goals table is as simple as suggested, it serves no purpose but I suspect this was simplification for the sake of providing an example. Assuming there is a player and time goal scored as part of the goal table, it has obvious value.

Goals

With the teams identified in the match you can then use something like -

select m.id as match_id,
  case
    when count(distinct hg.id) > count(distinct ag.id) then ht.name
    when count(distinct hg.id) < count(distinct ag.id) then at.name
    else 'draw'
  end as `result`
from matches m
join teams ht on m.home_team_id = ht.id
join teams at on m.away_team_id = at.id
left join goals hg on m.home_team_id = hg.team_id and m.id = hg.match_id
left join goals ag on m.away_team_id = ag.team_id and m.id = ag.match_id
group by m.id

to get -

Here's a db<>fiddle to play around with.