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.