SQL Server：联接表导致数据在每一行上重复

Question

我的这个查询语句返回了一些冗余的数据。请有经验的开发者帮我检查并改正该查询语句。我也附上了表格关系图。

SELECT
   dbo.supplierOrder.sectionID,
   dbo.supplierOrder.supplierID,
   dbo.supplierOrder.supplierOrderNo,
   dbo.supplierOrder.supplierOrderCreated,
   dbo.supplierOrder.supplierOrderConfirmStatus,
   dbo.supplierOrderDetails.productID,
   dbo.supplierOrderDetails.orderQty,
   dbo.supplierOrderReceive.invoiceno,
   dbo.supplierOrderReceive.supplierInvoiceno,
   dbo.supplierOrderReceive.ID,
   dbo.supplierOrderReceiveDetail.qtyArrived
FROM
   dbo.supplierOrder
INNER JOIN
   dbo.supplierOrderDetails ON (dbo.supplierOrderDetails.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
    dbo.supplierOrderReceive ON (dbo.supplierOrderReceive.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
    dbo.supplierOrderReceiveDetail ON (dbo.supplierOrderReceiveDetail.supplierOrderReceiveID = dbo.supplierOrderReceive.ID)       
WHERE
   dbo.supplierOrder.ID = 1 ; 

这是我得到的输出，但并不完全符合我的要求。最后一列qtyArrived应该只显示其相关数量，但如您所见，它在每个productID下都有重复。

以下是数据库表及其关系：

请参考此处的图片：

还有这张图片：

- ZAJ

Answer 1

我认为最后一个表也应该使用ProductId进行连接，而不仅仅是OrderID：

LEFT JOIN 
    dbo.supplierOrderReceiveDetail
ON  
(dbo.supplierOrderReceiveDetail.supplierOrderReceiveID=dbo.supplierOrderReceive.ID)
AND 
(dbo.supplierOrderReceiveDetail.ProductID =dbo.sdbo.supplierOrderDetails.ProductID)

- valex

---

我正要回答同样的问题！ :) - Maheswaran Ravisankar

感谢您的帮助！ - ZAJ