最多一条记录可以通过MS Access中的子查询返回 [英] At most one record can return by sub query in MS Access

问题描述

我需要从产品到达剩余库存。我有3个表产品，已售出，已收到产品



ProductStock表

Hi, I need to arrive Remaining Stock from products. I have 3 Tables Product, Sold, ReceivedProducts

ProductStock Table

ProductId   ProductName  InitialQuantity
1           Mobile       50
2           Charger      30
3           Torch Light  50
4           Laptop       40      

ReceivedProducts表

Product(Product Id)    ReceivedQty
2                      20
2                      5

已售出的表

ItemId   Quantity   Amount
4         3         90,000
1         10        5,000

现在我需要剩余的股票（（初始数量+收到数量） - 卖出数量

所以我尝试了这个查询加入初始数量和收到数量，但得到的错误像最多一条记录可以通过子查询返回

SELECT ProductId, InitialStock, 
(SELECT ReceivedQty FROM ReceivedProducts WHERE Product=ProductStock.ProductId) AS ReceivedStock FROM ProductStock;

以下查询工作但只获得一个产品，因为收到的产品是两次同样如此。如果收到的数量不可用，我需要添加初始库存。如何形成查询？

（初始数量+收到数量）

SELECT ProductId, 
InitialStock+SUM(ReceivedQty) AS TotalQty 
FROM ProductStock AS PSD INNER JOIN ReceivedProducts AS RP ON PSD.ProductId=RP.Product
GROUP BY PSD.ProductId,InitialStock;

帮我解决这个问题



提前致谢

Sri

Help me to solve this

Thanks in advance
Sri

推荐答案

你只得到一个产品因为你正在使用 INNER JOIN 。所以请改用 LEFT JOIN 。此外，您需要将 NULL 值替换为 0 。您的查询应如下所示：

Hi,

You're getting only one product because you're using INNER JOIN. So use LEFT JOIN instead. Also, you need to replace NULL values with 0. Your query should be like this:

SELECT ProductId, IIF(InitialStock IS NULL, 0, InitialStock) + SUM(IIF(ReceivedQty IS NULL, 0, ReceivedQty)) AS TotalQty
FROM ProductStock AS PSD LEFT JOIN ReceivedProducts AS RP ON PSD.ProductId = RP.Product
GROUP BY PSD.ProductId, InitialStock;

请告诉我它的工作原理。

Please let me know how it works.

这篇关于最多一条记录可以通过MS Access中的子查询返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！