[SOLVED] Query Help..missing data

[ninedoors]

OK, I am trying to pull out scores from a database from my hockey league.

 

I am using this query to get them:

 

"SELECT sch.game_id, sch.game_date, tsch.home, t.alt_name, COUNT(scr.action_id) as goals FROM smf_osm_schedule as sch
            LEFT JOIN smf_osm_team_schedule as tsch ON(sch.game_id = tsch.game_id)
            LEFT JOIN smf_osm_teams as t ON(tsch.team_id = t.team_id) 
            LEFT JOIN smf_osm_scoring as scr ON(tsch.game_id = scr.game_id AND tsch.team_id = scr.team_id)
            WHERE sch.entered = 1 AND scr.action_id = 1 AND sch.event_id = 2
            GROUP BY sch.game_id, sch.game_date, tsch.home, t.alt_name 
            ORDER BY sch.game_date DESC 
            LIMIT 0, 16";

 

Which will pull out 8 games for me.  The structure of the realtive table is below:

 

-- Table structure for table `smf_osm_schedule`
--
  `game_id` int(11) NOT NULL auto_increment,
  `event_id` smallint(5) unsigned NOT NULL,
  `reg_play` varchar(5) NOT NULL default 'r',
  `game_date` datetime NOT NULL,
  `location` varchar(100) NOT NULL,
  `division` varchar(10) NOT NULL,
  `finished` tinyint(4) NOT NULL default '0',
  `ref_1` int(11) NOT NULL,
  `ref_2` int(11) NOT NULL,
  `time_keeper` int(11) NOT NULL,
  `entered` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`game_id`),
  KEY `event_id` (`event_id`)

-- --------------------------------------------------------

--
-- Table structure for table `smf_osm_scoring`
--
  `id` int(10) unsigned NOT NULL auto_increment,
  `team_id` smallint(5) unsigned NOT NULL,
  `id_member` int(10) unsigned NOT NULL,
  `game_id` int(10) unsigned NOT NULL,
  `action_id` smallint(6) NOT NULL COMMENT '1 is goal, 2 is 1st assist, 3 is 2nd assist, 4 is so goal, 5 is so attempt',
  `action_type` varchar(10) NOT NULL COMMENT 'r,ot,rpp1,rpp2,otpp1, otpp2,rsh1,rsh2,otsh1,otsh2, any number is used for so order',
  `game_time` smallint(5) unsigned NOT NULL,
  `period` tinyint(4) NOT NULL COMMENT '0 is shootout',
  PRIMARY KEY  (`id`),
  KEY `team_id` (`team_id`),
  KEY `member_team` (`id_member`,`team_id`),
  KEY `game_id` (`game_id`),
  KEY `action_type` (`action_type`),
  KEY `action_id` (`action_id`)

-- --------------------------------------------------------

--
-- Table structure for table `smf_osm_teams`
--
  `team_id` smallint(5) unsigned NOT NULL auto_increment,
  `event_id` smallint(5) unsigned NOT NULL,
  `career_id` smallint(5) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `alt_name` varchar(50) NOT NULL,
  `team_rep` int(11) unsigned NOT NULL,
  `division` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`team_id`),
  KEY `event_id` (`event_id`),
  KEY `career_id` (`career_id`)

-- --------------------------------------------------------

--
-- Table structure for table `smf_osm_team_schedule`
--
  `id` int(10) unsigned NOT NULL auto_increment,
  `team_id` smallint(5) unsigned NOT NULL,
  `game_id` int(10) unsigned NOT NULL,
  `home` tinyint(3) unsigned NOT NULL COMMENT '0 is away, 1 is home',
  `shots` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `team_id` (`team_id`),
  KEY `game_id` (`game_id`)

 

So the problem I am having is when a team scores no goals that row is not fetched and my output only has one team.  How would I go about included the rows that have a COUNT of 0 or how would I have to reconstruct my query to get out the info I need, which is game_id, game_date, home team, home score, away team, away score.

 

Is this possible with one query or should I just resort to 2 queries?  Thanks for the help.

 

Nick

[ninedoors]

So I just want to see what I would get if I left off the join to the scoring table and I get back everything I need.  I used this query:

 

"SELECT sch.game_id, sch.game_date, tsch.home, t.alt_name FROM smf_osm_schedule as sch

            LEFT JOIN smf_osm_team_schedule as tsch ON(sch.game_id = tsch.game_id)

            LEFT JOIN smf_osm_teams as t ON(tsch.team_id = t.team_id)           

            WHERE sch.entered = 1 AND sch.event_id = 2

            GROUP BY sch.game_id, sch.game_date, tsch.home, t.alt_name

            ORDER BY sch.game_date DESC

            LIMIT 0, 16"

 

So I would like this result combined with the COUNT(action_id) from the scoring table.  I would realy like to do it with one query if it is possible.  Thanks again for any help.

 

Nick

[DavidAM]

Since the scoring table is the only one that might have no entries for the game_id - team_id combination, I think that is the only one that needs the LEFT JOIN.  However, it should work the way you wrote it with one or possibly two changes.

 

Where you have

AND scr.action_id = 1 AND

you are excluding any results where there are no goals scored.  In the outer join, if no rows match, all columns of that table will be NULL, so I would try:

 

AND (scr.action_id = 1 OR scr.action_id IS NULL) AND

 

since the action_id is NULL, the count may not work properly (I don't remember if mysql will count NULL columns or not, if not you can change the COUNT() to:

SUM(IF(scr.action_id, 1, 0)) as goals

which will add 1 for every non-NULL action_id it finds (and we have already limited it to action_id 1 or NULL) so the others will not be counted.

 

Good Luck!

[ninedoors]

Thanks David.  It worked perfect.  I am fairly new to JOINS and the advanced features of mysql.  I didn't know about the IF syntax so I am now off to google that.

 

Thanks again.

 

Nick