Keith...you are the man!!! Thanks for showing me a better way to solve my
problem. I honestly did not think of using correlated subqueries, mainly
because I was unsure of the underlying operations when they are used. But your
mentoring explanation has made it quite clear now. I hope to be as good as you
are...one day :-).
Be well.
> Subject: RE: UEFA EURO 2012 Football Championship problem
> Date: Mon, 11 Aug 2014 17:47:30 -0600
> From: kmedc...@dessus.com
> To: eem...@hotmail.com
> CC: sqlite-users@sqlite.org
>
> 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.
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
