how to write this query using joins?

Question

Hi, i have a table campaign which has details of campaign mails sent.

campaign_table:    campaign_id  campaign_name flag
                         1          test1       1
                         2          test2       1
                         3          test3       0 

another table campaign activity which has details of campaign activities.

 campaign_activity:  campaign_id   is_clicked    is_opened  
                          1             0            1        
                          1             1            0        
                          2             0            1
                          2             1            0

I want to get all campaigns with flag value 3 and the number of is_clicked columns with value 1 and number of columns with is_opened value 1 in a single query.

ie.   campaign_id  campaign_name  numberofclicks  numberofopens
           1          test1            1                1
           2          test2            1                1

I did this using sub-query with the query:

select c.campaign_id,c.campaign_name,
(SELECT count(campaign_id) from campaign_activity WHERE campaign_id=c.id AND is_clicked=1) as numberofclicks,
(SELECT count(campaign_id) from campaign_activity WHERE campaign_id=c.id AND is_clicked=1) as numberofopens
FROM
campaign c
WHERE c.flag=1

But people say that using sub-queries are not a good coding convention and you have to use join instead of sub-queries. But i don't know how to get the same result using join. I consulted with some of my colleagues and they are saying that its not possible to use join in this situation. Is it possible to get the same result using joins? if yes, please tell me how.

Answer 1

I suppose this should do it :

select * from campaign_table inner join campaign_activity on campaign_table.id = campaign_activity.id where campaign_table.flag = 3 and campaign_activity.is_clicked = 1 and campaign_activity.is_opened = 1

Attn : this is not tested in a live situation

Answer 2

Hmm. Is what you want as simple as this? I'm not sure I'm reading the question right...

SELECT
  campaign_table.campaign_id, SUM(is_clicked), SUM(is_opened)
FROM
  campaign_table 
    INNER JOIN campaign_activity ON campaign_table.campaign_id = campaign_activity.campaign_id
WHERE
  campaign_table.flag = 1
GROUP BY 
  campaign_table.campaign_id

Note that with an INNER JOIN here, you won't see campaigns where there's nothing corresponding in the campaign_activity table. In that circumstance, you should use a LEFT JOIN, and convert NULL to 0 in the SUM, e.g. SUM(IFNULL(is_clicked, 0)).

Answer 3

Assuming is_clicked and is_opened are only ever 1 or 0, this should work:

select c.campaign_id, c.campaign_name, sum(d.is_clicked), sum(d.is_opened)
from campaign c inner join campaign_activity d 
on c.campaign_id = d.campaign_id
where c.flag = 1
group by c.campaign_id, c.campaign_name

No sub-queries.

Answer 4

This should do the trick. Substitute INNER JOIN for LEFT OUTER JOIN if you want to include campaigns which have no activity.

SELECT
    c.Campaign_ID
    , c.Campaign_Name
    , SUM(CASE WHEN a.Is_Clicked = 1 THEN 1 ELSE 0 END) AS NumberOfClicks
    , SUM(CASE WHEN a.Is_Opened = 1 THEN 1 ELSE 0 END) AS NumberOfOpens
FROM 
    dbo.Campaign c
INNER JOIN
    dbo.Campaign_Activity a
ON  a.Campaign_ID = c.Campaign_ID
GROUP BY
    c.Campaign_ID
    , c.Campaign_Name

Answer 5

The SQL in it's simplest form and most robust form is this: (formatted for readability)

SELECT campaign_table.campaign_ID, campaign_table.campaign_name, Sum(campaign_activity.is_clicked) AS numberofclicks, Sum(campaign_activity.is_open) AS numberofopens

FROM campaign_table INNER JOIN campaign_activity ON campaign_table.campaign_ID = campaign_activity.campaign_ID

GROUP BY campaign_table.campaign_ID, campaign_table.campaign_name, campaign_table.flag

HAVING campaign_table.flag=1;