LEFT JOIN and COUNT with different number of rows

Question

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

Answer 1

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