Query Help

[Hooker]

Hey Everyone,

 

I'm building a cricket website for my local cricket league so the kids can see their stats etc and I've just about built everything but the league table and wondered if i could get a bit of help/advice on the best way to build it.

 

At the moment the tables look like this:

 

CREATE TABLE `Team` (
  `uid` int(11) unsigned NOT NULL auto_increment,
  `teamname` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`uid`)
) TYPE=MyISAM;

CREATE TABLE `results` (
  `uid` int(11) unsigned NOT NULL auto_increment,
  `league1` varchar(5) NOT NULL default '',
  `team1` varchar(100) NOT NULL default '',
  `team2` varchar(100) NOT NULL default '',
  `team1_score` varchar(100) NOT NULL default '',
  `team2_score` varchar(100) NOT NULL default '3',
  `date` date NOT NULL default '0000-00-00',
  `deleted` int(1) unsigned NOT NULL,
  PRIMARY KEY  (`uid`)
) TYPE=MyISAM;

 

There are 3 divisions of the league (A,B,C) and what i basically want to do is grab all the data from the correct league that isn't deleted, could up how many games/wins/draws/losses/points for/points against that a team has and order it based on who has the highest points (1 point for a draw, 3 for a win, 0 for a loss) using points for - points against as a tie breaker for teams with equal points then display them like this:

 

Team Name | Played | Win | Loss | Draw | PF | PA | Points

 

I can't quite get my mind around the most efficient way to do this without a million and one queries and using PHP to do half of the actual leg work so any help is greatly appreciated.

 

Thanks in advance!

 

 

[GingerRobot]

As a start, you might like to take a look at this very good tutorial

 

Incidentally, you'd be better off storing the team's IDs in the results table, rather than their names. You'd also be better off storing their score an an int.

[Hooker]

Thanks for the reply, i edited the structure as corrected, what i really wanted to know is how i'd go about calculating the wins/losses/draws fields (and their points based on 0 for a loss, 1 for a draw and 3 for a win) based on the results of all their games with Mysql/PhP as there are up to 20 teams at a time in each league.

[Hooker]

Okay, so this is where i'm at now:

Tables:

CREATE TABLE IF NOT EXISTS team (
  uid int(11) unsigned NOT NULL auto_increment,
  tname varchar(100) NOT NULL,
  PRIMARY KEY  (uid)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE results (
  uid int(11) unsigned NOT NULL auto_increment,
  league varchar(1) NOT NULL,
  hteam int(11) unsigned NOT NULL,
  hscore int(11) unsigned NOT NULL,
  ateam int(11) unsigned NOT NULL,
  ascore int(11) unsigned NOT NULL,
  `date` date NOT NULL default '0000-00-00',
  `status` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (uid)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Query:

SELECT tname, Sum(P),Sum(W),Sum(D),Sum(L),Sum(Q),SUM(F),SUM(A),SUM(GD),SUM(Pts) FROM(SELECT hteam Team, 1 P,
IF (hscore > ascore,1,0) W,
IF (hscore = ascore AND hscore <> '0',1,0) D,
IF (hscore = ascore AND hscore = '0',1,0)Q,
IF (hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE WHEN hscore > ascore THEN 6 WHEN hscore = ascore AND hscore <> '0' THEN 3 WHEN hscore = ascore AND hscore = '0' THEN 1 ELSE 0 END PTS
FROM results
UNION ALL
SELECT ateam,
1,
IF (hscore < ascore,1,0),
IF (hscore = ascore AND ascore <> '0',1,0),
IF (hscore = ascore AND ascore = '0',1,0),
IF (hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE WHEN hscore < ascore THEN 6 WHEN hscore = ascore AND ascore <> '0' THEN 3 WHEN hscore = ascore AND ascore = '0' THEN 1 ELSE 0 END
FROM results) as tot, team WHERE tot.Team=team.uid GROUP BY Team ORDER BY SUM(Pts) DESC;

 

My only problem is this:

Team3  5  5  0  0  0  21  10  11  30

Team1 6 2 0 3 1 14 11 55340232221128654851 13

Team4 6 2 0 4 0 11 22 73786976294838206453 12

Team2 5 1 0 3 1 9 12 55340232221128654845 7

 

Can anyone give me any info on why the Goal Difference isnt being calculated properly for all teams?

 

Also any help improving the query would be greatly appreciated!

 

P.S - "IF (hscore = ascore AND hscore = '0',1,0)Q," is for abandoned games purely because abandoned games award 1 point to each team.

 

Thanks in advance!

 

 

[fenway]

Which side of the UNION is generating this errant value?

[Hooker]

not sure exactly what you mean sorry

[fenway]

a UNION combines two select statements -- which one of these generates that funny value?

[Hooker]

Top part of the union generates:

Team1  3  2  0  1  0  11  6  18446744073709551621  12

Team3 2 2 0 0 0 7 2 5 12

Team4 3 2 0 1 0 7 8 18446744073709551615 12

Team2 3 1 0 1 1 7 7 18446744073709551616 7

 

Bottom part of the union generates:

Team3  3  3  0  0  0  14  8  6  18

Team1 3 0 0 2 1 3 5 36893488147419103230 1

Team4 3 0 0 3 0 4 14 55340232221128654838 0

Team2 2 0 0 2 0 2 5 36893488147419103229 0

 

so the answer is.. both?

[Hooker]

It seems to only get the first row returned right.

[Hooker]

I went for this instead and it returns the right information:

SELECT tname AS TEAM, Sum( P ) AS P, Sum( W ) AS W, Sum( D ) AS D, Sum( L ) AS L, Sum( Q ) AS Q, SUM( F ) AS F, SUM( A ) AS A, SUM( F ) - SUM( A ) AS GD, SUM( Pts ) AS PTS
FROM (

SELECT hteam Team, 1P, IF( hscore > ascore, 1, 0 ) W, IF( hscore = ascore
AND hscore <> '0', 1, 0 ) D, IF( hscore = ascore
AND hscore = '0', 1, 0 ) Q, IF( hscore < ascore, 1, 0 ) L, hscore F, ascore A,
CASE WHEN hscore > ascore
THEN 6
WHEN hscore = ascore
AND hscore <> '0'
THEN 3
WHEN hscore = ascore
AND hscore = '0'
THEN 1
ELSE 0
END PTS
FROM results
UNION ALL
SELECT ateam, 1, IF( hscore < ascore, 1, 0 ) , IF( hscore = ascore
AND ascore <> '0', 1, 0 ) , IF( hscore = ascore
AND ascore = '0', 1, 0 ) , IF( hscore > ascore, 1, 0 ) , ascore, hscore,
CASE WHEN hscore < ascore
THEN 6
WHEN hscore = ascore
AND ascore <> '0'
THEN 3
WHEN hscore = ascore
AND ascore = '0'
THEN 1
ELSE 0
END
FROM results
) AS tot, team
WHERE tot.Team = team.uid
GROUP BY Team
ORDER BY SUM( Pts ) DESC , GD DESC ;

LIMIT 0 , 30 

 

Anyone have any suggestions to improve the query?

 

Any help is realyl appreciated!

[Mr Rich UK]

Did you get this to work at all?

 

I'm trying to do a similar thing, but some of the things you have put in here confused me.  You mention 1P at one time.... what is that?