mysql - MySQL内部连接导致乘法

基本上我有这样的结构：
交易有多种渠道，且属于多种渠道
交易有很多交易
交易属于渠道
当我想找到交易卖出的金额时，我使用以下查询：

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales
INNER JOIN deals ON deals.id = deal_sales.deal_id
INNER JOIN targets ON deals.target_id = targets.id
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY targets.id

它的工作原理很好，问题是当我需要按渠道筛选时，找到渠道中交易的销售额：

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales
INNER JOIN deals ON deals.id = deal_sales.deal_id
INNER JOIN targets ON deals.target_id = targets.id
**INNER JOIN channels_deals ON channels_deals.deal_id = deals.id**
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY targets.id

当我将join添加到channels表中时，卖出的数量将乘以与某个交易相关的每个channel。我怎样才能避免这个？

最佳答案

使用或存在
例如

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales
INNER JOIN deals ON deals.id = deal_sales.deal_id
INNER JOIN targets ON deals.target_id = targets.id

WHERE targets.approved = 1 AND targets.active = 1
  and
   deals.id IN (SELECT deal_id from channels_deals where something = 1)
GROUP BY targets.id