Re: [sqlite] UEFA EURO 2012 Football Championship problem - SOLVED!!!
Thanks Petite...You, like Keith Medcalf, are a great mentor, helping me to develop my burgeoning skills in SQL. I appreciate the reason for the cause of each of the problems I encountered and how to correct them. 'Granularity' is a new term for me and I now know why multiple entries occurred. Keith earlier introduced single 'scalar' result of correlated sub-queries. Also, I am now more aware of the type of join that should be used to include desired rows. Be well. > From: petite.abei...@gmail.com > Date: Mon, 11 Aug 2014 21:39:29 +0200 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] UEFA EURO 2012 Football Championship problem > > > On Aug 11, 2014, at 8:39 PM, Errol Emden 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. > _ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UEFA EURO 2012 Football Championship problem - SOLVED!!
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
