CODEWITHSUNDEEP
sqlmysqldatabase
aquero
aquero

Reputation: 863

how to write this query using joins?

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.

Upvotes: 2

Views: 351

Answers (5)

sjmoore.co.uk
sjmoore.co.uk

Reputation: 1

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;

Upvotes: 0

Mark Storey-Smith
Mark Storey-Smith

Reputation: 997

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

Upvotes: 4

Matt Gibson
Matt Gibson

Reputation: 38238

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)).

Upvotes: 2

tzaman
tzaman

Reputation: 47860

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.

Upvotes: 4

Soldierflup
Soldierflup

Reputation: 114

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

Upvotes: 0

Related Questions