一个left join 的问题（结果翻倍）
我现在有三张表：
Product 表 : id，qty
  1 100
  2 200

Inventory表（库存） ：product_id , qty ,warehouse_id
  1 20 1
  1 30 2
  2 50 1

Sell 表（销售）： product_id, qty
  1 20
  1 10
   

现在要查询出 产品id，总数量，库存量，未入库量，销售量 
要用一个产品就一条数据表示（group by ）。用如下的sql ，查询出来，产品id 为 1的库存量会算成100 ，本来应该是50的，会翻倍算，要如何写sql ？

   

SELECT product.id,
  product.name,
  product.qty AS totalqty,
  if(sum(inventory.qty) IS NULL, 0, sum(inventory.qty)) AS inventory_qty,
  if(sum(sell.qty) IS NULL, 0, sum(sell.qty)) AS sellqty,
  product.qty - IF(sum(inventory.qty) IS NULL, 0, sum(inventory.qty)) -
  IF(sum(sell.qty) IS NULL, 0, sum(sell.qty)) AS out_inventory_qty
  FROM product
  LEFT JOIN inventory
  ON product.id = inventory.product_id
  LEFT JOIN sell
  ON product.id = sell.product_id
 GROUP BY product.id, product.name

------解决方案--------------------
先对Inventory表SUM，然后再JOIN

select 
from aaa left join (select id,sum() from Inventory group by ..) x
------解决方案--------------------

SQL code

select  A.id,A.qty as 总数量,B.qty as 库存量,A.qty-B.qty as 未入库量,C.qty as 销售量
from product A left join (select product_id,sum(qty) as qty from invertory group by product_id)B
on A.id = B.prduct_id left join sell C on A.id = C.id

------解决方案--------------------
你下把前面个left 看做一个结果集然后在和sell做连接！