How do I write this query using connections?
I have a campaign with a table that lists the details of the campaign emails sent.
campaign_table: campaign_id campaign_name flag
1 test1 1
2 test2 1
3 test3 0
other campaign activity in a table detailing campaign activities.
campaign_activity: campaign_id is_clicked is_opened
1 0 1
1 1 0
2 0 1
2 1 0
I want all campaigns with flag value 3 and number of columns is_clicked with value 1 and number of columns with is_opened value equal to 1 in one query.
ie. campaign_id campaign_name numberofclicks numberofopens
1 test1 1 1
2 test2 1 1
I did it using a subquery with a 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 subqueries is not a good coding convention and you should use union instead of subqueries. But I don't know how to get the same result using join. I have consulted with some of my colleagues and they say that it cannot be used in this situation. Can you get the same result using joins? if yes, please tell me.
a source to share
This should do the trick. Replace INNER JOIN for LEFT OUTER JOIN if you want to include campaigns that 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
a source to share
Assuming is_clicked
and is_opened
will only be 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
There are no subqueries.
a source to share
Hmm. Is this what you want as simple as this? I'm not sure I'm reading the question correctly ...
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 here with an INNER JOIN you won't see any campaigns that don't have anything matching in the campaign_activity table. In this case, you have to use LEFT JOIN and convert NULL to 0 in SUM for example. SUM (IFNULL (is_clicked, 0)).
a source to share
SQL in it's simplest form and most reliable form: (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;
a source to share