MS Access中的子查询错误最多只能返回一条记录？ [英] At most one record can return by sub query error in MS Access ?

问题描述

您好我尝试从一个表中获取数量但是我得到这样的错误最多一个记录可以通过子查询返回吗？我的代码出了什么问题？

  SELECT  ProductCode，InitialStock， （ SELECT 数量 FROM  NewInvoice_2  WHERE  NewInvoice_2。 Item = Product.ProductCode） AS  StockRemaining 
  FROM 产品; 

这是我的产品表

 PoductId ProductName ProductCode InitialStock 
  1  product1 P101  10  
  2  product2 p102  15  

这是我的发票表

< pre lang =SQL> InvoiceNo ProductCode数量
Inv-111 P101 3
Inv-112 P1 02 5
Inv-113 P102 3

现在我需要得到这样的结果

 ProductCode InitialStock StockRemaining 
 P101  10   7  
 P102  15   7  

我需要得到这样的结果。如何形成此任务的查询？帮帮我



预付谢谢

Srihari

解决方案

试试这个：

 选择 p.productcode，initialstock，initialstock  -  sum（qty） AS  stockremaining 
 来自产品p  join  invoice i   p.productcode = i.productcode  group   by  p .productcode，initialstock 

Hi I try to get the number of quantities from one table but am getting error like this At most one record can return by sub query ? What is wrong with my code ?

SELECT ProductCode, InitialStock, (SELECT Quantity FROM NewInvoice_2 WHERE NewInvoice_2.Item=Product.ProductCode) AS StockRemaining
FROM Product;

this is my Product table

PoductId     ProductName    ProductCode    InitialStock
1            product1       P101           10
2            product2       p102           15        

this is my invoice table

InvoiceNo    ProductCode    Qty
Inv-111      P101           3
Inv-112      P102           5
Inv-113      P102           3

Now i need to get result like this

ProductCode    InitialStock   StockRemaining
P101           10             7
P102           15             7

I need to get result like this. How to form query for this task ? Help me

Thanks in Advance
Srihari

解决方案

Try this:

select p.productcode, initialstock, initialstock - sum(qty) AS stockremaining
from product p join invoice i on p.productcode = i.productcode group by p.productcode, initialstock

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