使用条件访问多个连接

Question

我正在生成一个库存查询，下面的代码(主要是)工作，但它包括发票已作废，导致负库存。

Void Yes/No field = tblInvoices.Void

tblInvoiceDetails.InvoiceNum = tblInvoices.ID

我不知道如何确保这不包括发票被作废。提前感谢您的帮助！

SELECT tblInventory.ID, Nz(sumTotalPaid,0)-Nz(sumCreditAmount,0) AS Quantity
FROM (tblInventory 
LEFT JOIN (
    SELECT ProductID, Sum(Quantity) AS sumTotalPaid
    FROM tblOrderDetails
    GROUP BY tblOrderDetails.ProductID
)  AS sum1 
   ON tblInventory.ID = sum1.ProductID) 
LEFT JOIN (
    SELECT ProductID, Sum(Quantity) AS sumCreditAmount
    FROM tblInvoiceDetails
    GROUP BY tblInvoiceDetails.ProductID
)  AS sum2 
    ON tblInventory.ID = sum2.ProductID;

Answer 1

这样试一试：

SELECT tblInventory.ID, Nz(sumTotalPaid,0)-Nz(sumCreditAmount,0) AS Quantity
FROM (tblInventory  
JOIN (
    SELECT ProductID, Sum(Quantity) AS sumTotalPaid
    FROM tblOrderDetails
    GROUP BY tblOrderDetails.ProductID
)  AS sum1 
   ON tblInventory.ID = sum1.ProductID) 
JOIN (
    SELECT ProductID, Sum(Quantity) AS sumCreditAmount
    FROM tblInvoiceDetails
    WHERE tblInvoiceDetails.InvoiceNum IN 
        (SELECT tblInvoices.ID
        FROM tblInvoices
        WHERE tblInvoices.Void='Yes')
    GROUP BY tblInvoiceDetails.ProductID
)  AS sum2 
    ON tblInventory.ID = sum2.ProductID

1.-首先您只使用JOIN而不是LEFT JOIN，所以您只需要得到两个表中都有值的行。

2.-你只得到有tblInvoices.Void='Yes'的产品