MySQL count, left join, group by to return zero rows

Question

In the below sql statement:

 SELECT `keywords`.keyID, count(`keywords-occurencies`.keyID) as countOccurencies 
                    FROM `keywords-occurencies`  
                    LEFT JOIN `keywords` 
                    ON `keywords-occurencies`.keyID = `keywords`.keyID 
                    WHERE `keywords-occurencies`.`keyID` IN (1,2,3) AND date BETWEEN '2013/01/25' AND '2013/01/27'
                    GROUP BY `keywords`.`keyID`

If keyID 3 has no return values it is not counted as 0 and it is not included in the result set and a result like this is displayed

keyID countOccurencies
1       3
3       5

I would like to display the zero results like

keyID countOccurencies
1       3
2       0
3       5

Sample data to test with:

--
-- Table structure for table `keywords`
--

CREATE TABLE IF NOT EXISTS `keywords` (
  `keyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `keyName` varchar(40) NOT NULL,
  PRIMARY KEY (`keyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `keywords`
--

INSERT INTO `keywords` (`keyID`, `keyName`) VALUES
(1, 'testKey1'),
(2, 'testKey2');

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

--
-- Table structure for table `keywords-occurencies`
--

CREATE TABLE IF NOT EXISTS `keywords-occurencies` (
  `occurencyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `keyID` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`occurencyID`),
  KEY `keyID` (`keyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `keywords-occurencies`
--

INSERT INTO `keywords-occurencies` (`occurencyID`, `keyID`, `date`) VALUES
(1, 1, '2013-01-27'),
(2, 1, '2013-01-26');

--
-- Constraints for table `keywords-occurencies`
--
ALTER TABLE `keywords-occurencies`
  ADD CONSTRAINT `keywords@002doccurencies_ibfk_1` FOREIGN KEY (`keyID`) REFERENCES `keywords` (`keyID`) ON DELETE CASCADE ON UPDATE CASCADE;

Answer 1

things to do

• you should be grouping it with GROUP BY keywords-occurencies.keyID
• and you must be displaying keywords-occurencies.keyID not the keywords.keyID
• COUNT keywords.keyID
• (optional) use ALIAS so you can get rid of the backticks other than tableNames

query,

SELECT  a.keyID,
        count(b.keyID) AS countOccurencies
FROM    `keywords - occurencies` a
        LEFT JOIN `keywords` b
            ON a.keyID = b.keyID
WHERE   a.keyID IN ( 1, 2, 3 ) AND 
        DATE BETWEEN '2013/01/25' AND '2013/01/27'
GROUP   BY a.keyID

UPDATE 1

Based on the example records, you need to do the following,

• interchange the tableNames
• put this condition DATE BETWEEN '2013-01-25' AND '2013-01-27' on the ON clause of join.
• (optional) use ALIAS so you can get rid of the backticks other than tableNames

query,

SELECT  a.keyID,
        count(b.keyID) AS countOccurencies
FROM    `keywords` a
        LEFT JOIN `keywords-occurencies` b
            ON a.keyID = b.keyID AND
               b.DATE BETWEEN '2013-01-25' AND '2013-01-27'
WHERE   a.keyID IN ( 1, 2, 3 ) 
GROUP   BY a.keyID

• SQLFiddle Demo