Sql JOIN给出了重复的reocrds？ [英] Sql JOIN giving repeated reocrds ?

问题描述

您好，

我已经为某些输出创建了sp，如下所示，但我从我的sp获取重复记录。我在我的sp中加入错误的东西。请解决我的问题。

Hello,
I have created sp for some output as following, But i am getting the repeated records from my sp. I thing i am putting wrong joins in my sp. Please solve my problem.

ALTER procedure [dbo].[GetSupplierToVendorDetails]
@did varchar(50)
as 
begin

SELECT ROW_NUMBER() OVER(ORDER BY  stvd.SrNo) AS SrNo, stvd.Product_Id, p.Product_Name, stvd.Quantity, vcd.Quantity AS DispatchedQty
FROM         Tbl_Dispatch_SupplierToVendor_Details AS stvd INNER JOIN
                      Tbl_ProductMaster AS p ON stvd.Product_Id = p.Product_Id INNER JOIN
                      Tbl_Dispatch_VendorToClient AS vc ON stvd.SupplierToVendor_Id = vc.SupplierToVendor_Id INNER JOIN
                      Tbl_Dispatch_VendorToClient_Details AS vcd ON vcd.VendorToClient_Id = vc.VendorToClient_Id
WHERE     (stvd.SupplierToVendor_Id = @did)

SrNo	Product_Id	Product_Name	Quantity	DispatchedQty
1	TVP2	      Bearing rod	  400	           200
2	TVP2	      Bearing rod	  400	           300
3	TVS1	        shaft	          500	           200
4	TVS1	        shaft	          500	           300

我得到的输出如上。但是例外情况如下：

And I am getting output like above. But the excepted out put is like following

SrNo	Product_Id	Product_Name	Quantity	DispatchedQty
1	TVP2	      Bearing rod	  400	           200
2	TVS1	        shaft	          500	           300

以下是表结构图像：

[表格结构图片]



我尝试过：

Following is the table structure image:
[Table Structure Image]

What I have tried:

ALTER procedure [dbo].[GetSupplierToVendorDetails]
@did varchar(50)
as 
begin

SELECT ROW_NUMBER() OVER(ORDER BY  stvd.SrNo) AS SrNo, stvd.Product_Id, p.Product_Name, stvd.Quantity, vcd.Quantity AS DispatchedQty
FROM         Tbl_Dispatch_SupplierToVendor_Details AS stvd INNER JOIN
                      Tbl_ProductMaster AS p ON stvd.Product_Id = p.Product_Id INNER JOIN
                      Tbl_Dispatch_VendorToClient AS vc ON stvd.SupplierToVendor_Id = vc.SupplierToVendor_Id INNER JOIN
                      Tbl_Dispatch_VendorToClient_Details AS vcd ON vcd.VendorToClient_Id = vc.VendorToClient_Id
WHERE     (stvd.SupplierToVendor_Id = @did)

推荐答案

我想说与Tbl_Dispatch_VendorToClient_Details表连接需要与Tbl_Dispatch_SupplierToVendor_Details相关的product_id的新关系，但在不知道表格结构的情况下很难知道...

I would say that join with "Tbl_Dispatch_VendorToClient_Details" table needs a new relationship with "product_id" related to "Tbl_Dispatch_SupplierToVendor_Details" but it's difficult to know without knowing the tables structure...

试试这个：

Try this:

ALTER procedure [dbo].[GetSupplierToVendorDetails]
@did varchar(50)
as 
begin
 
SELECT ROW_NUMBER() OVER(ORDER BY  stvd.SrNo) AS SrNo, stvd.Product_Id, p.Product_Name, stvd.Quantity, vcd.Quantity AS DispatchedQty
FROM         Tbl_Dispatch_SupplierToVendor_Details AS stvd INNER JOIN
                      Tbl_ProductMaster AS p ON stvd.Product_Id = p.Product_Id INNER JOIN
                      Tbl_Dispatch_VendorToClient AS vc ON stvd.SupplierToVendor_Id = vc.SupplierToVendor_Id INNER JOIN
                      Tbl_Dispatch_VendorToClient_Details AS vcd ON vcd.VendorToClient_Id = vc.VendorToClient_Id and 
vcd.Product_id=stvd.Product_Id
WHERE     (stvd.SupplierToVendor_Id = @did)

这篇关于Sql JOIN给出了重复的reocrds？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！