How Can I Join 3 Tables And Calculate The Correct Sum Of Fields From 2 Tables, Without Duplicate Rows?

I have tables A, B, C. Table A is linked to B, and table A is linked to C. I want to join the 3 tables and find the sum of B.cost and the sum of C.clicks. However, it is not giving

Solution 1:

If I understand the logic correctly, the problem is the Cartesian product caused by the two joins. Your query is a bit hard to follow, but I think the intent is better handled with correlated subqueries:

select k.*,
       (selectsum(cost)
        from ad_group_keyword_network n
        where n.event_date >= '2015-12-27'and
              n.ad_group_keyword_id = 1210802and
              k.id = n.ad_group_keyword_id
       ) as cost,
       (selectsum(clicks)
        from keyword_click c
        where (c.date isnullor c.date >= '2015-12-27') and
              k.keyword_id = c.keyword_id               
       ) as clicks
from ad_group_keyword k
where k.status = 2 ;

Here is the corresponding SQL Fiddle.

EDIT:

The subselect should be faster than the group by on the unaggregated data. However, you need the right indexes: ad_group_keyword_network(ad_group_keyword_id, ad_group_keyword_id, event_date, cost) and keyword_click(keyword_id, date, clicks).

Solution 2:

I found this (MySQL joining tables group by sum issue) and created a query like this

select * 
from A
join (select B.a_id, sum(B.cost) as cost 
  from B 
  groupby B.a_id) B on A.id = B.a_id
left join (select C.keyword_id, sum(C.clicks) as clicks
  from C
  groupby C.keyword_id) C on A.keyword_id = C.keyword_id
groupby A.id
having sum(cost) > 10

I don't know if it's efficient though. I don't know if it's more or less efficient than Gordon's. I ran both queries and this one seemed faster, 27s vs. 2m35s. Here is a fiddle: http://sqlfiddle.com/#!15/c61c74/10

Solution 3:

Simply split the aggregate of the second table into a subquery as follows:

http://sqlfiddle.com/#!9/768745/27

select ad_group_keyword.*, SumCost, sum(keyword_click.clicks) 
from ad_group_keyword 
left join keyword_click on ad_group_keyword.keyword_id = keyword_click.keyword_id 
left join (select ad_group_keyword.id, sum(cost) SumCost
           from ad_group_keyword join ad_group_keyword_network on ad_group_keyword.id = ad_group_keyword_network.ad_group_keyword_id
           where event_date >= '2015-12-27'groupby ad_group_keyword.id
           having sum(cost) > 20
) Cost on Cost.id=ad_group_keyword.id
where  
(keyword_click.date isnullor keyword_click.date >= '2015-12-27') 
and status = 2groupby ad_group_keyword.id