Re: [sqlite] UEFA EURO 2012 Football Championship problem - SOLVED!!!

[Errol Emden]

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 <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.
> _
> 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!!

[Errol Emden]

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

Re: [sqlite] UEFA EURO 2012 Football Championship problem

[Keith Medcalf]

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 2012ESP 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

Re: [sqlite] UEFA EURO 2012 Football Championship problem

[Petite Abeille]

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.

selectgame.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

Re: [sqlite] UEFA EURO 2012 Football Championship problem

[Errol Emden]

Hi Keith:
I am trying to list every match with the goals scored by each team as shown.
mdateteam1score1team2score2
1 July 2012ESP4ITA 0
10 June 2012ESP1ITA1
10 June 2012IRL1CRO3
...
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