CODEWITHSUNDEEP
phpmysql
cantaffordavan
cantaffordavan

Reputation: 1447

Select all from first table, count instances of ID in second table

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 am not too familiar with JOIN and UNION so I have been experimenting and reading through docu's but I can't quite figure out how to implement COUNT with them.

This is what I have so far, but everytime I try and add in the COUNT function I get errors and can't figure it out:

SELECT * FROM `campaigns` LEFT JOIN `subscribers` ON `campaigns`.id = `subscribers`.campaign;

EDIT A query posted below kind of works, but it excludes additional campaigns that have a count of zero

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

Upvotes: 2

Views: 1434

Answers (4)

Vinodkumar SC
Vinodkumar SC

Reputation: 323

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

this query will give what you expect...

Upvotes: 1

cantaffordavan
cantaffordavan

Reputation: 1447

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

Upvotes: 2

Rubin Porwal
Rubin Porwal

Reputation: 3845

As a solution to your problem please try executing below sql query

select c.number,keyword,(select count(id) as count from subscribers
where campaign=c.id) from  campaigns c

Upvotes: 0

Skynet
Skynet

Reputation: 578

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

is what you need. The only thing that does not work is to show "pie" with 0 subscribers... I'm unfortunately not into mysql sub-selects which is what you need for this.

Upvotes: 0

Related Questions