How about
select mdate,
team1,
(select count(*)
from goal
where matchid = game.id
and teamid = game.team1) as score1,
team2,
(select count(*)
from goal
where matchid = game.id
and teamid = game.team2) as score2
from game
order by mdate, id, team1, team2;
the parts "(select ...) as scoreX" are called Correlated Subqueries.
Basically, you are retrieving your game table and columns that you want from
it. For each returned row each Subquery is executed to return the result
computed for the value(s) contained in that row. A Correlated Subquery may
only return a single scalar result (ie, only one row containing one value). If
a subquery returns multiple rows, only the first value is used (that is, it is
as if the Correlated Subqueries had "limit 1" appended to them). Selecting
multiple values in a correlated subquery is a syntax error.
>-----Original Message-----
>From: Errol Emden [mailto:eem...@hotmail.com]
>Sent: Monday, 11 August, 2014 12:39
>To: Keith Medcalf
>Cc: General Discussion of SQLite Database
>Subject: RE: UEFA EURO 2012 Football Championship problem
>
>Hi Keith:
>I am trying to list every match with the goals scored by each team as
>shown.
>
>mdate team1 score1 team2 score2
>1 July 2012 ESP 4 ITA 0
>10 June 2012 ESP 1 ITA 1
>10 June 2012 IRL 1 CRO 3
>...
>
>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
