[Resolved] 2nd try - Retrieving team records with game results

mrbaseball34

MySQL version 4.3.8

I am able to return the number of games completed each week
using this SQL:

select g.gamenum,
      DATEFORMAT(g.gamedate, '%W, %M %e') as gamedate,
      g.homeid,
      h.teamname home,
      g.homescore hscore,
      g.visitorid,
      v.teamname visitor,
      g.visitorscore vscore
from games g,
     teams v, teams h
where (g.homeid = h.id and g.visitorid = v.id) 
  and (WEEK(g.gamedate) = WEEK(CURDATE()))
  and ( g.homescore is not null and g.visitorscore is not null  )
order by gamedate

Now I need to also return the win. loss, and tie records for each
team up to the CURDATE.

Here is how I do it normally. I pass each record returned from the SQL
above to a function that builds the records.

   /* 
       999 as score means rainout, 777 as score means game 
       cancelled or moved 
   */ 
    $rsql = "SELECT homescore, visitorscore, homeid, visitorid  

              FROM games 
              WHERE ( homeid={$item['homeid']} OR visitorid={$item['homeid']} ) 
                AND homescore <> 999 AND visitorscore <> 999 
                AND homescore <> 777 AND visitorscore <> 777 
                AND ( homescore <> ' ' ANDvisitorscore <> ' ' ) 
                AND ( homescore IS NOT NULL AND visitorscore IS NOT NULL ) 
                AND gamedate <= '{$item['gamedate']}' 
                order by gamedate";

Then I walk the result and increment wins, losses or ties based
upon the result.

Could I do both of these operations with just one SQL statement and return
the results sorted by points? Parameters to the query should be division and
date.

mrbaseball34

#
# Table structure for table 'games'
#

CREATE TABLE games (
  gameid int(11) NOT NULL auto_increment,
  gamenum int(11) NOT NULL default '0',
  gamedate datetime NOT NULL default '0000-00-00 00:00:00',
  visitorid int(11) NOT NULL default '0',
  homeid int(11) NOT NULL default '0',
  visitorscore int(11) default NULL,
  homescore int(11) default NULL,
  fieldid int(11) NOT NULL default '0',
  modified smallint(6) default NULL,
  PRIMARY KEY  (gameid)
) TYPE=MyISAM


#
# Dumping data for table 'games'
#

INSERT INTO `games` VALUES (1, 1, '2004-04-03 10:00:00', 17, 5, 0, 12, 5, NULL);
INSERT INTO `games` VALUES (2, 2, '2004-04-03 13:00:00', 40, 41, 11, 1, 5, NULL);
INSERT INTO `games` VALUES (3, 3, '2004-04-03 16:00:00', 19, 29, 6, 5, 5, NULL);
INSERT INTO `games` VALUES (4, 4, '2004-04-04 10:00:00', 35, 24, 13, 1, 1, NULL);
INSERT INTO `games` VALUES (5, 5, '2004-04-04 13:00:00', 12, 28, 5, 2, 1, NULL);
INSERT INTO `games` VALUES (6, 6, '2004-04-04 16:00:00', 27, 2, 14, 4, 1, NULL);
INSERT INTO `games` VALUES (7, 7, '2004-04-04 10:00:00', 23, 30, 9, 10, 5, NULL);
INSERT INTO `games` VALUES (8, 8, '2004-04-04 13:00:00', 34, 26, 6, 0, 5, NULL);
INSERT INTO `games` VALUES (9, 9, '2004-04-04 16:00:00', 14, 41, 17, 3, 5, NULL);
INSERT INTO `games` VALUES (10, 10, '2004-04-04 10:00:00', 38, 43, 1, 12, 6, NULL);
INSERT INTO `games` VALUES (11, 11, '2004-04-04 13:00:00', 39, 7, 3, 9, 6, NULL);
INSERT INTO `games` VALUES (12, 12, '2004-04-04 16:00:00', 33, 15, 5, 2, 6, NULL);
INSERT INTO `games` VALUES (13, 13, '2004-04-04 10:00:00', 13, 1, 8, 5, 7, NULL);
INSERT INTO `games` VALUES (14, 14, '2004-04-04 13:00:00', 44, 20, 7, 2, 7, NULL);
INSERT INTO `games` VALUES (15, 15, '2004-04-04 16:00:00', 18, 25, 6, 8, 7, NULL);
INSERT INTO `games` VALUES (16, 16, '2004-04-05 19:30:00', 17, 47, 12, 17, 8, NULL);
INSERT INTO `games` VALUES (17, 17, '2004-04-06 19:00:00', 36, 36, NULL, NULL, 5, NULL);
INSERT INTO `games` VALUES (18, 18, '2004-04-06 19:30:00', 36, 36, NULL, NULL, 8, NULL);
INSERT INTO `games` VALUES (19, 19, '2004-04-07 19:00:00', 36, 36, NULL, NULL, 5, NULL);
INSERT INTO `games` VALUES (20, 20, '2004-04-07 19:30:00', 36, 36, NULL, NULL, 6, NULL);
INSERT INTO `games` VALUES (21, 21, '2004-04-07 19:30:00', 40, 45, 15, 2, 8, 1);
INSERT INTO `games` VALUES (22, 22, '2004-04-08 19:00:00', 13, 29, 10, 5, 5, NULL);
INSERT INTO `games` VALUES (23, 23, '2004-04-08 19:30:00', 19, 33, 3, 15, 8, NULL);
INSERT INTO `games` VALUES (24, 24, '2004-04-09 19:30:00', 2, 27, 6, 4, 8, 1);
INSERT INTO `games` VALUES (25, 25, '2004-04-12 19:00:00', 14, 42, 999, 999, 5, NULL);
INSERT INTO `games` VALUES (26, 26, '2004-04-12 19:30:00', 31, 37, 6, 4, 8, NULL);
INSERT INTO `games` VALUES (27, 27, '2004-04-13 19:00:00', 17, 30, 9, 10, 5, 1);
INSERT INTO `games` VALUES (28, 28, '2004-04-13 19:30:00', 25, 46, 9, 13, 8, NULL);
INSERT INTO `games` VALUES (29, 29, '2004-04-14 19:00:00', 45, 14, 2, 10, 5, NULL);
INSERT INTO `games` VALUES (30, 30, '2004-04-14 19:30:00', 31, 42, 5, 5, 6, NULL);
INSERT INTO `games` VALUES (31, 31, '2004-04-14 19:30:00', 18, 39, 6, 9, 8, NULL);
INSERT INTO `games` VALUES (32, 32, '2004-04-15 19:00:00', 47, 46, 4, 5, 5, 1);
INSERT INTO `games` VALUES (33, 33, '2004-04-15 19:30:00', 40, 20, 12, 6, 8, NULL);
INSERT INTO `games` VALUES (34, 34, '2004-04-16 19:00:00', 27, 7, 11, 20, 5, NULL);
INSERT INTO `games` VALUES (35, 35, '2004-04-16 19:30:00', 41, 38, 17, 9, 8, NULL);
INSERT INTO `games` VALUES (36, 36, '2004-04-17 10:00:00', 2, 5, 4, 16, 5, 1);
INSERT INTO `games` VALUES (37, 37, '2004-04-17 13:00:00', 1, 24, 16, 1, 5, NULL);
INSERT INTO `games` VALUES (38, 38, '2004-04-17 16:00:00', 43, 40, 5, 10, 5, NULL);
INSERT INTO `games` VALUES (39, 39, '2004-04-18 10:00:00', 41, 12, 0, 9, 1, NULL);
INSERT INTO `games` VALUES (40, 40, '2004-04-18 13:00:00', 18, 27, 1, 7, 1, NULL);
INSERT INTO `games` VALUES (41, 41, '2004-04-18 16:00:00', 35, 13, 3, 0, 1, NULL);
INSERT INTO `games` VALUES (42, 42, '2004-04-18 10:00:00', 7, 25, 16, 6, 5, NULL);
INSERT INTO `games` VALUES (43, 43, '2004-04-18 13:00:00', 40, 45, 9, 5, 5, NULL);
INSERT INTO `games` VALUES (44, 44, '2004-04-18 16:00:00', 19, 24, 10, 3, 5, NULL);
INSERT INTO `games` VALUES (45, 45, '2004-04-18 10:00:00', 20, 38, 14, 2, 6, NULL);
INSERT INTO `games` VALUES (46, 46, '2004-04-18 13:00:00', 29, 15, 13, 10, 6, NULL);
INSERT INTO `games` VALUES (47, 47, '2004-04-18 16:00:00', 23, 39, 4, 1, 6, NULL);
INSERT INTO `games` VALUES (48, 48, '2004-04-18 10:00:00', 46, 47, 3, 10, 7, NULL);
INSERT INTO `games` VALUES (49, 49, '2004-04-18 13:00:00', 42, 34, 2, 6, 7, NULL);
INSERT INTO `games` VALUES (50, 50, '2004-04-18 16:00:00', 37, 44, 0, 5, 7, NULL);
INSERT INTO `games` VALUES (51, 51, '2004-04-19 19:00:00', 12, 43, 14, 5, 5, NULL);
INSERT INTO `games` VALUES (52, 52, '2004-04-19 19:30:00', 33, 1, 8, 15, 8, NULL);
INSERT INTO `games` VALUES (53, 53, '2004-04-20 19:00:00', 5, 23, 16, 7, 5, NULL);
INSERT INTO `games` VALUES (54, 54, '2004-04-20 19:30:00', 20, 45, 15, 16, 8, 1);
INSERT INTO `games` VALUES (55, 55, '2004-04-21 19:00:00', 35, 15, 8, 7, 5, NULL);
INSERT INTO `games` VALUES (56, 56, '2004-04-21 19:30:00', 24, 13, 0, 16, 6, NULL);
INSERT INTO `games` VALUES (57, 57, '2004-04-21 19:30:00', 25, 39, 0, 7, 8, 1);
INSERT INTO `games` VALUES (58, 58, '2004-04-22 19:00:00', 28, 26, 6, 5, 5, NULL);
INSERT INTO `games` VALUES (59, 59, '2004-04-22 19:30:00', 46, 17, 9, 5, 8, NULL);
INSERT INTO `games` VALUES (60, 60, '2004-04-23 19:00:00', 5, 27, 2, 8, 5, 1);
INSERT INTO `games` VALUES (61, 61, '2004-04-23 19:30:00', 30, 7, 7, 1, 8, 1);
INSERT INTO `games` VALUES (62, 62, '2004-04-24 10:00:00', 40, 37, 999, 999, 5, NULL);
INSERT INTO `games` VALUES (63, 63, '2004-04-24 13:00:00', 2, 39, 999, 999, 5, NULL);
INSERT INTO `games` VALUES (64, 64, '2004-04-24 16:00:00', 14, 31, 999, 999, 5, NULL);
INSERT INTO `games` VALUES (65, 65, '2004-04-25 10:00:00', 23, 25, 999, 999, 1, NULL);
INSERT INTO `games` VALUES (66, 66, '2004-05-08 10:00:00', 15, 19, 2, 4, 6, 1);
INSERT INTO `games` VALUES (67, 67, '2004-04-25 16:00:00', 45, 38, 999, 999, 1, NULL);
INSERT INTO `games` VALUES (68, 68, '2004-04-25 10:00:00', 44, 42, 999, 999, 7, NULL);
INSERT INTO `games` VALUES (69, 69, '2004-04-25 13:00:00', 1, 35, 999, 999, 7, NULL);
INSERT INTO `games` VALUES (70, 70, '2004-04-25 16:00:00', 20, 12, 999, 999, 7, NULL);
INSERT INTO `games` VALUES (71, 71, '2004-04-26 19:00:00', 29, 33, 7, 6, 5, NULL);
INSERT INTO `games` VALUES (72, 72, '2004-04-26 19:30:00', 12, 45, 11, 8, 8, NULL);
INSERT INTO `games` VALUES (73, 73, '2004-04-27 19:00:00', 40, 26, 5, 6, 5, NULL);
INSERT INTO `games` VALUES (74, 74, '2004-04-27 19:30:00', 14, 42, 16, 0, 8, 1);
INSERT INTO `games` VALUES (75, 75, '2004-04-28 19:00:00', 34, 40, 1, 5, 5, 1);
INSERT INTO `games` VALUES (76, 76, '2004-04-28 19:30:00', 20, 41, 8, 2, 6, NULL);
INSERT INTO `games` VALUES (77, 77, '2004-04-28 19:30:00', 1, 19, 13, 10, 8, NULL);
INSERT INTO `games` VALUES (78, 78, '2004-04-29 19:00:00', 26, 37, 8, 3, 5, NULL);
INSERT INTO `games` VALUES (79, 79, '2004-04-29 19:30:00', 45, 31, 12, 13, 8, NULL);
INSERT INTO `games` VALUES (80, 80, '2004-04-30 19:00:00', 30, 27, 4, 5, 5, NULL);
INSERT INTO `games` VALUES (81, 81, '2004-04-30 19:30:00', 7, 25, 7, 0, 8, 1);
INSERT INTO `games` VALUES (82, 82, '2004-05-01 10:00:00', 35, 29, 999, 999, 5, NULL);
INSERT INTO `games` VALUES (83, 83, '2004-05-01 13:00:00', 44, 40, 999, 999, 5, NULL);
INSERT INTO `games` VALUES (84, 84, '2004-05-01 16:00:00', 39, 5, 999, 999, 5, NULL);
INSERT INTO `games` VALUES (85, 85, '2004-05-02 10:00:00', 24, 33, 13, 15, 1, NULL);
INSERT INTO `games` VALUES (86, 86, '2004-05-02 13:00:00', 38, 28, 2, 16, 1, NULL);
INSERT INTO `games` VALUES (87, 87, '2004-05-02 16:00:00', 23, 47, 1, 3, 1, NULL);
INSERT INTO `games` VALUES (88, 88, '2004-05-02 10:00:00', 34, 42, 0, 4, 5, NULL);
INSERT INTO `games` VALUES (89, 89, '2004-05-02 13:00:00', 45, 20, 6, 5, 5, NULL);
INSERT INTO `games` VALUES (90, 90, '2004-05-02 16:00:00', 15, 13, 3, 11, 5, NULL);
INSERT INTO `games` VALUES (91, 91, '2004-05-02 10:00:00', 18, 46, 0, 0, 6, NULL);
INSERT INTO `games` VALUES (92, 92, '2004-05-08 13:00:00', 27, 46, 8, 6, 6, 1);
INSERT INTO `games` VALUES (93, 93, '2004-05-08 16:00:00', 12, 42, 17, 4, 6, 1);
INSERT INTO `games` VALUES (94, 94, '2004-05-08 10:00:00', 35, 29, 14, 1, 7, 1);
INSERT INTO `games` VALUES (95, 95, '2004-05-08 13:00:00', 2, 17, 777, 777, 7, NULL);
INSERT INTO `games` VALUES (96, 96, '2004-05-08 16:00:00', 41, 44, 0, 15, 7, NULL);
INSERT INTO `games` VALUES (97, 97, '2004-05-03 19:00:00', 8, 41, 12, 8, 5, NULL);
INSERT INTO `games` VALUES (98, 98, '2004-05-03 19:30:00', 25, 7, 2, 7, 8, NULL);
INSERT INTO `games` VALUES (99, 99, '2004-05-04 19:00:00', 18, 23, 9, 10, 5, NULL);
INSERT INTO `games` VALUES (100, 100, '2004-05-04 19:30:00', 27, 47, 7, 1, 8, NULL);

mrbaseball34

Accumulative Standings for the data above:
(
wins count for 3 pts, ties count for 1 pt and
losses don't count at all
)

Austin Division:
Team         W  L  T  pts
------------ -  -  -  ---
Bandits      0  3  0  0 
Devil Rays   3  0  0  9 
Express      1  3  1  4 
Rangers      2  2  0  6
Royals       2  0  1  7 
White Sox    2  2  0  6

Capitol Division:
Team         W  L  T  pts
------------ -  -  -  ---
Astros       1  2  0  3 
Bombers      3  1  0  9 
Braves       5  1  0  15 
Cubs         3  2  0  9 
Dodgers      0  4  0  0 
Giants       0  4  0  0 
Orioles      2  3  0  6 
Pirates      1  5  0  3 
Red Sox      6  2  0  18 
Reds         2  2  0  6 
Rockies      3  1  0  9 
Zephyrs      3  2  0  9 

Lone Star Division:
Team         W  L  T  pts
------------ -  -  -  ---
Angels       3  1  0  9 
Curve        4  1  0  12
Diamondbacks 0  5  0  0
Indians      3  2  0  9 
Padres       0  5  0  0 
River Dogs   2  3  0  6 
Twins        3  2  0  9 
Yankees      4  0  0  12 

MABL Division:
Team          W  L  T  pts
------------  -  -  -  ---
Brewers       1  0  0  3  

Cardinals     5  0  0  15  

Colt 45s      0  4  0  0  

Dirt Bags     1  2  0  3  

Green Jackets 1  6  0  3  

Indians       2  4  0  6  

Isotopes      3  0  0  9  

Legends       2  5  0  6  

Red Wings     2  1  0  6  

Texans        6  1  0  18   

Data and structure for Teams:

#
# Table structure for table 'teams'
#

CREATE TABLE teams (
  id int(1) NOT NULL auto_increment,
  teamname varchar(20) NOT NULL default '',
  division char(1) NOT NULL default '',
  active tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY teamname (teamname)
) TYPE=MyISAM;

#
# Dumping data for table 'teams'
#

INSERT INTO teams VALUES("1", "Angels", "L", "1");
INSERT INTO teams VALUES("2", "Astros", "C",  "1");
INSERT INTO teams VALUES("37", "Bandits", "A", "1");
INSERT INTO teams VALUES("5", "Bombers", "C", "1");
INSERT INTO teams VALUES("7", "Braves", "C", "1");
INSERT INTO teams VALUES("8", "Brewers", "M", "1");
INSERT INTO teams VALUES("12", "Cardinals", "M", "1");
INSERT INTO teams VALUES("38", "Colt 45s", "M", "1");
INSERT INTO teams VALUES("46", "Cubs", "C", "1");
INSERT INTO teams VALUES("13", "Curve", "L", "1");
INSERT INTO teams VALUES("14", "Devil Rays", "A", "1");
INSERT INTO teams VALUES("15", "Diamondbacks", "L", "1");
INSERT INTO teams VALUES("43", "Dirt Bags", "M", "1");
INSERT INTO teams VALUES("17", "Dodgers", "C", "1");
INSERT INTO teams VALUES("42", "Express", "A", "1");
INSERT INTO teams VALUES("18", "Giants", "C", "1");
INSERT INTO teams VALUES("41", "Green Jackets", "M", "1");
INSERT INTO teams VALUES("19", "Indians", "L", "1");
INSERT INTO teams VALUES("20", "Indians", "M", "1");
INSERT INTO teams VALUES("44", "Isotopes", "M", "1");
INSERT INTO teams VALUES("45", "Legends", "M", "1");
INSERT INTO teams VALUES("23", "Orioles", "C", "1");
INSERT INTO teams VALUES("24", "Padres", "L", "1");
INSERT INTO teams VALUES("25", "Pirates", "C", "1");
INSERT INTO teams VALUES("26", "Rangers", "A", "1");
INSERT INTO teams VALUES("27", "Red Sox", "C", "1");
INSERT INTO teams VALUES("28", "Red Wings", "M", "1");
INSERT INTO teams VALUES("39", "Reds", "C", "1");
INSERT INTO teams VALUES("29", "Riverdogs", "L", "1");
INSERT INTO teams VALUES("30", "Rockies", "C", "1");
INSERT INTO teams VALUES("31", "Royals", "A", "1");
INSERT INTO teams VALUES("40", "Texans", "M", "1");
INSERT INTO teams VALUES("33", "Twins", "L", "1");
INSERT INTO teams VALUES("34", "White Sox", "A", "1");
INSERT INTO teams VALUES("35", "Yankees", "L", "1");
INSERT INTO teams VALUES("47", "Zephyrs", "C", "1");

tomhath

I don't follow all of what you are trying to do, but this should be most of it. You'll need to add the date part:

select t.teamname,
      t.division,
      sum(case 
            when g.homeid = t.id and g.homescore > g.visitorscore then 1
            when g.visitorid = t.id and g.homescore < g.visitorscore then 1
            else 0
          end) as win,
      sum(case 
            when g.homeid = t.id and g.homescore < g.visitorscore then 1
            when g.visitorid = t.id and g.homescore > g.visitorscore then 1
            else 0
          end) as loss,
      sum(case 
            when g.homeid = t.id and g.homescore = g.visitorscore then 1
            else 0
          end) as tie,
      sum(case 
            when g.homeid = t.id and g.homescore > g.visitorscore then 3
            when g.visitorid = t.id and g.homescore < g.visitorscore then 3
            when g.homescore = g.visitorscore then 1
            else 0
          end) as points
from games g,
     teams t
where (g.homeid = t.id or g.visitorid = t.id) 
  and ( g.homescore is not null and g.visitorscore is not null  )
  and g.homescore not in (999,777)
group by t.division, t.teamname
order by 2, 6 desc, 1;

mrbaseball34

Added this clause to get by division:

and t.division='A'

Here are the mistakes:

Austin Division:
Royals are not awarded a tie for the game .vs Express even tho Express is awarded a tie.

Capitol Division:
Cubs somehow awarded a tie even though they didn't have any
during the entire posted schedule.

MABL Division, Lone Star Division:
no mistakes seen

Can you figure this one out? I can't see it.

tomhath

Yup, that's a bug, only the home team got credit for a tie.

This should fix it (untested):

sum(case  

            when (g.homeid = t.id or g.visitorid = t.id ) and g.homescore = g.visitorscore then 1 
            else 0 
          end) as tie,

The Cubs do have a tie here, don't they?
INSERT INTO games VALUES (91, 91, '2004-05-02 10:00:00', 18, 46, 0, 0, 6, NULL);

mrbaseball34

I forgot about that game. If we set the score to 0-0, it means a double forfeit and the games is essentially neglected. I already
added code to remove that anomaly.

Many thanks for the help.

tomhath

You didn't ask but I'll offer the advice anyway. Every column in the database should store one type of information; if you try to use the same column for two different things you're asking for bugs.

Add another column to the 'games' table to indicate whether the game was a played, forfeited, cancelled, etc. instead of using fake scores the way you have it now.

mrbaseball34

Originally posted by tomhath
This should fix it (untested):

Nope, this didn't either. It still counts the tie in the points but
doesn't show a tie in the standings.

But, here is the final product:

select t.teamname, 
      sum(case  

            when g.homeid = t.id and g.homescore > g.visitorscore then 1 
            when g.visitorid = t.id and g.homescore < g.visitorscore then 1 
            else 0 
          end) as win, 
      sum(case  

            when g.homeid = t.id and g.homescore < g.visitorscore then 1 
            when g.visitorid = t.id and g.homescore > g.visitorscore then 1 
            else 0 
          end) as loss, 
      sum(case   

            when (g.homeid = t.id or g.visitorid = t.id ) and 
                 ((g.homescore = g.visitorscore) and  

                  (g.homescore > 0 and g.visitorscore > 0 )) then 1  

            else 0  

          end) as tie,
      sum(case  

            when g.homeid = t.id and g.homescore > g.visitorscore then 3 
            when g.visitorid = t.id and g.homescore < g.visitorscore then 3 
            when ((g.homescore = g.visitorscore) and  

                  (g.homescore > 0 and g.visitorscore > 0 )) then 1  

            else 0 
          end) as points 
from games g, 
     teams t 
where (g.homeid = t.id or g.visitorid = t.id)  

  and (g.homescore is not null and g.visitorscore is not null  ) 
  and g.homescore not in (999,777) 
  and t.division = %DIVISION
group by t.division, t.teamname 
order by 5 desc, 3, 1;