CODEWITHSUNDEEP
sqlsql-server
ZAJ
ZAJ

Reputation: 835

SQL Server : join tables causes the data to duplicate on every row

I have this query which is returning some redundant data. Can somebody please help me to correct this query. I have also attached the table relationship.

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 ;

here is the output that I get

which is not exactly what I was want. the last column qtyArrived should show only its related qty but here as you can see it is repeating for each of the productID

enter image description here

and here is the db tables and relationship enter image description here

enter image description here

Upvotes: 3

Views: 85

Answers (1)

valex
valex

Reputation: 24144

I think the last table should be join using also ProductId not only OrderID :

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

Upvotes: 1

Related Questions