i've got a table campaign that has particulars of campaign mails sent.

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

another table campaign activity that has particulars of campaign activities.

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

I wish to get all campaigns with flag value 3 and the amount of is_clicked on posts with value 1 and quantity of posts with is_opened up value 1 in one query.

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

Used to do this using sub-query using 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
campaign c
WHERE c.flag=1

But people state that using sub-queries are a bad coding convention and make use of join rather than sub-queries. But i'm not sure ways to get exactly the same result using join. I consulted with a few of my co-workers and they're stating that it is not easy to use participate in this case. Can you really obtain the same result using joins? if so, please let me know how.

Presuming is_clicked and is_opened are just ever 1 or , this will 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.

This will have the desired effect. Substitute INNER JOIN for LEFT OUTER JOIN if you wish to include campaigns without any activity.

    , 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
    dbo.Campaign c
    dbo.Campaign_Activity a
ON  a.Campaign_ID = c.Campaign_ID
    , c.Campaign_Name

Hmm. Is what you would like as easy as this? I am unsure I am reading through the question right...

  campaign_table.campaign_id, SUM(is_clicked), SUM(is_opened)
    INNER JOIN campaign_activity ON campaign_table.campaign_id = campaign_activity.campaign_id
  campaign_table.flag = 1

Observe that by having an INNER JOIN here, you will not see campaigns where there is nothing corresponding within the campaign_activity table. For the reason that circumstance, you need to use a LEFT JOIN, and convert NULL to within the SUM, e.g. SUM(IFNULL(is_clicked on, )).

I guess this will get it done :

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 isn't examined inside a live situation

The SQL in it's easiest form and many robust form is: (formatted for readability)

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

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

campaign_table.campaign_ID, campaign_table.campaign_name, campaign_table.flag