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 
ORDER BY c.id ASC
share|improve this question

1 Answer 1

up vote 2 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
LEFT JOIN (
  SELECT Campaign, COUNT(*) AS count
  FROM Subscribers
  GROUP BY Campaign
) AS s
ON c.id = s.Campaign
ORDER BY c.id ASC
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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