On Aug 11, 2014, at 8:39 PM, Errol Emden <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
