如何為以下問題編寫mysql查詢？

具有項目名稱的產品表table1

table2有目前庫存

table3有採購庫存

我想要的結果

我嘗試的查詢無法在最終結果中從表 3 中檢索行，這就是我得到的

SELECT 
  *

FROM
  table2 a
      LEFT JOIN
  table3 b ON a.item_id = b.item_id
       JOIN
  table1 d ON  d.item_id=a.item_id
  ORDER BY a.item_id

SELECT item_cid, 
      item_id, 
      item_name,
      SUM(table2.current_stock) current_stock,
      SUM(table3.purchase_qty) purchase_qty,
      COALESCE(SUM(table2.current_stock), 0) + COALESCE(SUM(table3.purchase_qty), 0) total
FROM table1
LEFT JOIN table2 USING (item_cid, item_id, item_name)
LEFT JOIN table3 USING (item_cid, item_id, item_name)
GROUP BY item_cid, item_id, item_name

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=aeac4b2165c8b8370fbbc082f08b3145

PS。您的表格未標準化。

引用自：https://dba.stackexchange.com/questions/308594