Sql JOIN giving repeated reocrds ?

Question

0.00/5 (No votes)

See more:

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.

SQL

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)

C#

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

c#"

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:

SQL

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)

Posted 29-Nov-16 20:00pm

Veeshal Mali

Updated 30-Nov-16 20:04pm

v2

Add a Solution

Comments

Tomas Takac 30-Nov-16 2:23am

Clearly it's the last join to Tbl_Dispatch_VendorToClient_Details that is causing you problems. You need to analyze the data to find out why.

Veeshal Mali 30-Nov-16 2:38am

I analyze and replace this join with other joins. But the output is same sir...

jamuro77 30-Nov-16 3:11am

Could you add the tables structure related to your query? If not, It's diffcult to know...

Veeshal Mali 1-Dec-16 1:08am

Following is the table structure Image sir...
click For image

F-ES Sitecore 30-Nov-16 4:03am

You have two records in the Tbl_Dispatch_VendorToClient_Details table for that VendorToclient_id so you're generating two rows of the join. Either the data is at fault or there is something in Tbl_Dispatch_VendorToClient_Details that you need to use to identify which of the rows you want to join on, ie the "200" row for Bearing Rod rather than "300".

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jamuro77 · Accepted Answer · 2016-11-29T21:19:00

Solution 1

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...

Posted 29-Nov-16 21:19pm

jamuro77

jamuro77 · Accepted Answer · 2016-11-30T20:04:00

Try this:

SQL

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)