title: mysql gives NULL for record in a table if JOIN and COUNT used but SELECT works fine why?
body: <p>Table 1</p>
  
  <pre><code> CREATE TABLE IF NOT EXISTS `com_msg` (
   `msg_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `msg_to` int(10) NOT NULL,
   `msg_from` int(10) NOT NULL,
   `msg_new` tinyint(1) unsigned NOT NULL DEFAULT '1',
   `msg_content` varchar(300) NOT NULL,
   `msg_date` date NOT NULL,
   `bl_sender` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `bl_recip` tinyint(1) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`msg_id`),
   UNIQUE KEY `msg_id` (`msg_id`)
   ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Table 2</p>
  
  <pre><code> CREATE TABLE IF NOT EXISTS `ac_vars` (
   `user_id` int(10) unsigned NOT NULL,
   `ac_ballance` smallint(3) unsigned NOT NULL DEFAULT '0',
   `prof_views` mediumint(8) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`user_id`),
   UNIQUE KEY `id` (`user_id`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>When I use query :</p>
  
  <pre><code>SELECT ac_ballance, prof_views, COUNT( msg_id ) AS messages
  FROM ac_vars
  INNER JOIN com_msg ON user_id = msg_to 
  WHERE user_id =".$userid." AND com_msg.msg_new =1;
  </code></pre>
  
  <p>I get :</p>
  
  <pre><code>ac_ballance=NULL(incorrect)
  prof_views=NULL(incorrect)
  messages=0(correct)
  </code></pre>
  
  <p>But with Select statement just on ac_vars I get correct values, what is the correct way of doing this? </p>