LEFT JOIN and COUNT with different number of rows

I have two tables, the first table contains numbers with unique ID's and a keyword and the second table contains numbers that are linked back to the first table by the unique ID (in the CAMPAIGN column). I want to get each of the numbers from the first table and count how many times their unique ID (in CAMPAIGN) shows up in the second table (ie: how many keywords each number has attached to it).

         CAMPAIGNS                                   SUBSCRIBERS
----------------------------------      --------------------------------------
ID    |    NUMBER    |  KEYWORD  |      | ID    |   NUMBER     |   CAMPAIGN  |
----------------------------------      --------------------------------------
1     |  +1222222222 |   pizza   |      |  22   | 555-333-222  |     2       |
----------------------------------      --------------------------------------
2     |  +1222222222 |   burger  |      |  21   | 222-333-222  |     2       |
----------------------------------      --------------------------------------
3     |  +1444444444 |    pie    |      |  33   | 333-111-111  |     1       |
----------------------------------
4     |  +1111111111 |  chicken  |


Results I need are:

----------------------------------------
NUMBER      |  KEYWORD   | COUNT |
----------------------------------------
+1222222222   |   pizza    |   1   |
----------------------------------------
+1222222222   |   burger   |   2   |
----------------------------------------
+1444444444   |    pie     |   0   |
----------------------------------------
+1111111111   |   chicken  |   0   |


I had been asking around and I found this solution below but it only shows the first result that has a s.ID count of zero and ignores additional ones. Can anyone see what is going wrong here? (The query below doesn't include the bottom result chicken)

SELECT c.number,c.id,c.keyword,COUNT(s.id) AS count
FROM campaigns AS c LEFT JOIN subscribers AS s
ON c.id = s.campaign GROUP BY s.campaign
ORDER BY c.id ASC

-

Not sure if your question fits the "Code Review" rules - seems more like a StackOverflow question.... But my answer will probably spawn some code reviews....

What if you left-join to a subselect of the counts from the Subscribers table? I believe this is OK in MySQL (I'm a T-SQL person):

SELECT c.number, c.id, c.keyword, ISNULL(s.count,0) AS count
FROM campaigns AS c
LEFT JOIN (
SELECT Campaign, COUNT(*) AS count
FROM Subscribers
GROUP BY Campaign
) AS s
ON c.id = s.Campaign
ORDER BY c.id ASC

-
This solved my problem! if possible can you explain why the previous code only showed one result with zero for count? –  mcflause Dec 31 '12 at 2:58
Glad it worked! I think your code wasn't working because in your GROUP BY, you were grouping by s.campaign. The possible values there are 1, 2 and NULL (because of the LEFT JOIN). You would get NULL for s.campaign for both "chicken" and "pie". This is a guess, but I'm assuming that the query optimizer simply picked one of those to show the zero count from the NULL values. Somebody who knows MySQL better than me could probably answer that more accurately. –  M Falanga Dec 31 '12 at 3:33