Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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 
share|improve this question

closed as off-topic by Simon André Forsberg Jul 23 at 10:49

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions containing broken code or asking for advice about code not yet written are off-topic, as the code is not ready for review. After the question has been edited to contain working code, we will consider reopening it." – Simon André Forsberg
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

up vote 5 down vote accepted

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
  SELECT Campaign, COUNT(*) AS count
  FROM Subscribers
  GROUP BY Campaign
) AS s
ON c.id = s.Campaign
share|improve this answer
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

Not the answer you're looking for? Browse other questions tagged or ask your own question.