On Aug 11, 2014, at 8:39 PM, Errol Emden <eem...@hotmail.com> wrote:
> 1. Matches in which neither team scored is not being displayed. Because you have an inner join to goal. If there no goal, then no entry will match. > 2. Scores for the same matchid where both teams scored are appearing on > separate lines instead of in a single line. Because you have a join to goal, which has a granularity of one entry per goal, per match. So, if multiple goal, multiple entries. You try to compensate by grouping per match and team, so you end up with two entries if both team have scored. > What do I need to do to correct these issues? Get you granularity in order. select game.mdate, game.matchid, game.team1, ( select count( * ) from goal where goal.matchid = game.id and goal.teamid = game.team1 ) as score1, game.team2, ( select count( * ) from goal where goal.matchid = game.id and goal.teamid = game.team2 ) as score2 from game order by game.mdate, game.matchid _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users