Hi Keith:
I am trying to list every match with the goals scored by each team as shown.
mdateteam1score1team2score2
1 July 2012ESP4ITA 0
10 June 2012ESP1ITA1
10 June 2012IRL1CRO3
...
The database schema is as follows:
game(id,mdate,stadium,team1,team2); goal(matchid,teamid,player,gtime);
eteam(id,teamname,coach). matchid=game.id; teamid=eteam.id; team1 and team2 are
teamids'; gtime is time each goal is scored in a match.
My problems in the SQL code below are:
1. Matches in which neither team scored is not being displayed.
2. Scores for the same matchid where both teams scored are appearing on
separate lines instead of in a single line.
What do I need to do to correct these issues?
SELECT mdate,
team1,
CASE WHEN teamid=team1 THEN count(gtime) ELSE 0 END score1,
team2,
CASE WHEN teamid=team2 THEN count(gtime) ELSE 0 END score2
FROM game JOIN goal ON matchid = id
GROUP BY matchid, teamid
ORDER BY mdate,matchid,team1,team2
Thanks and be well.
Errol
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
