mysql - 嵌套的 MySQL 在 Access VBA 中不起作用

Question

我正在尝试将我的查询更新到 MySQL，并且发现我需要使用嵌套查询来获得我需要的结果。

我有以下代码:-

SELECT tbl_invoices.ID, tbl_invoices.customer_id, (SELECT Sum(tbl_invoices_payments.payment_amount) AS SumOfpayment_amount FROM tbl_invoices_payments WHERE invoice_id = id GROUP BY tbl_invoices_payments.invoice_id ) AS Payments
FROM tbl_invoices
WHERE (((tbl_invoices.customer_id)=11));

我得到错误:-

2014 - 命令不同步；你现在不能运行这个命令

我需要的结果只是每张发票的所有付款的总和。

谁能指出我正确的方向？

Answer 1

SELECT tbl_invoices.ID, tbl_invoices.customer_id, Sum(tbl_invoices_payments.payment_amount) AS SumOfpayment_amount 
FROM tbl_invoices left outer join tbl_invoices_payments on tbl_invoices_payments.invoice_id = tbl_invoices.ID
GROUP BY tbl_invoices.ID
having tbl_invoices.customer_id=11;