https://sqlzoo.net/wiki/The_JOIN_operation
13
select mdate,team1,
sum (case when teamid= team1 then 1 else 0 end) score1,
team2,
sum (case when teamid= team2 then 1 else 0 end) score2
from game left join goal on (game.id=goal.matchid)
group by mdate,team1,team2