Help with Joins

[Cpreston]

Hi

I have some script that should only return 5 rows based on the PO number. However I seem to get back 10 rows. I believe the issue is around the join for GRN Values but have tried all combinations of the joins but cannot get the expected 5 rows. Does anyone see anything obvious I am missing or can do to sort the issue. Thanks

SELECT   dbo.PurchaseOrderHeader.PurchaseOrderNumber, dbo.PurchaseOrderHeader.TotalCostPrice, dbo.PurchaseOrderLine.TotalCostPrice AS TotalLineCostProce, 
                      dbo.PurchaseOrderHeader.OurRef, dbo.Supplier.Name, dbo.PurchaseOrderHeader.DateRequired, dbo.PurchaseOrderHeader.DateTimeCreated, 
                      dbo.Users.Name AS [Created By], dbo.StockReceiptLine.TotalCostPrice AS [GRN Value], dbo.StockReceiptHeader.DateReceived AS [GRN Date], 
                      dbo.StockReceiptHeader.StockReceiptNumber AS [GRN Ref], dbo.SupplierInvoiceHeader.DocumentNumber AS [Pinv N0], 
                      dbo.SupplierInvoiceHeader.TotalAmount AS [Pinv Value], dbo.SupplierInvoiceHeader.DocumentDate AS [Pinv Date], 
                      dbo.SupplierInvoiceHeader.DateTimeCreated AS [Date Time Created], dbo.SupplierInvoiceHeader.DateTimeLastModified
FROM         dbo.PurchaseOrderHeader Left JOIN
                      dbo.Users ON dbo.PurchaseOrderHeader.UserID = dbo.Users.UserID LEFT JOIN
                      dbo.PurchaseOrderLine ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.PurchaseOrderLine.PurchaseOrderID  JOIN
                      dbo.StockReceiptLine ON dbo.PurchaseOrderLine.PurchaseOrderLineID = dbo.StockReceiptLine.PurchaseOrderLineID AND 
                      dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.StockReceiptLine.PurchaseOrderID LEFT OUTER JOIN
                      dbo.Supplier ON dbo.PurchaseOrderHeader.SupplierID = dbo.Supplier.SupplierID LEFT OUTER JOIN
                      dbo.SupplierInvoiceHeader ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.SupplierInvoiceHeader.PurchaseOrderID Inner JOIN
                      dbo.StockReceiptHeader ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.StockReceiptHeader.PurchaseOrderID AND 
                      dbo.StockReceiptLine.StockReceiptID = dbo.StockReceiptHeader.StockReceiptID
                      WHERE     (dbo.PurchaseOrderHeader.PurchaseOrderNumber = 24839) AND dbo.StockReceiptLine.TotalCostPrice <>0

 

[bborissov]

If you post some example data that would be easier.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

I have attached a screen shot, not sure if it clear enough, it duplicates rows for some reason?

 

[Olaf Doschke]

I see double [GRN Value]s but differing [Pinv Value]s. Seems you have two records in dbo.SupplierInvoiceHeader

Bye, Olaf.

 

[Cpreston]

Yes this is correct but I only want it to show one record so I only have the 5 lines from the PO. Any way of doing that?

Thanks

 

[Olaf Doschke]

If you can't think of a condition filtering out the one of each pair of records, I don't know.

As the row doubling join is the join of dbo.SupplierInvoiceHeader, in the first place: Leave that out or find a more restricive join condition than only dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.SupplierInvoiceHeader.PurchaseOrderID. You could have a secondary join condition [highlight #FCE94F]AND dbo.PurchaseOrderHeader.somecolumn = dbo.SupplierInvoiceHeader.somecolumn[/highlight]

Not knowing table structures and relations defined this is all just guesswork. You have to dig deeper into the relations you have between the data. You can't fix a result merely from knowing the query. There is no obvious error to mend, here.

Removing that join will also drop some of the result columns of course, but as a starting point see whether this will result in half the number of rows:

SELECT   dbo.PurchaseOrderHeader.PurchaseOrderNumber, dbo.PurchaseOrderHeader.TotalCostPrice, dbo.PurchaseOrderLine.TotalCostPrice AS TotalLineCostProce, 
                      dbo.PurchaseOrderHeader.OurRef, dbo.Supplier.Name, dbo.PurchaseOrderHeader.DateRequired, dbo.PurchaseOrderHeader.DateTimeCreated, 
                      dbo.Users.Name AS [Created By], dbo.StockReceiptLine.TotalCostPrice AS [GRN Value], dbo.StockReceiptHeader.DateReceived AS [GRN Date], 
                      dbo.StockReceiptHeader.StockReceiptNumber AS [GRN Ref]
FROM         dbo.PurchaseOrderHeader Left JOIN
                      dbo.Users ON dbo.PurchaseOrderHeader.UserID = dbo.Users.UserID LEFT JOIN
                      dbo.PurchaseOrderLine ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.PurchaseOrderLine.PurchaseOrderID  JOIN
                      dbo.StockReceiptLine ON dbo.PurchaseOrderLine.PurchaseOrderLineID = dbo.StockReceiptLine.PurchaseOrderLineID AND 
                      dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.StockReceiptLine.PurchaseOrderID LEFT OUTER JOIN
                      dbo.Supplier ON dbo.PurchaseOrderHeader.SupplierID = dbo.Supplier.SupplierID Inner JOIN
                      dbo.StockReceiptHeader ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.StockReceiptHeader.PurchaseOrderID AND 
                      dbo.StockReceiptLine.StockReceiptID = dbo.StockReceiptHeader.StockReceiptID
                      WHERE     (dbo.PurchaseOrderHeader.PurchaseOrderNumber = 24839) AND dbo.StockReceiptLine.TotalCostPrice <>0

Bye, Olaf.

 

[Cpreston]

Hi

Ok I now have my code as below which is bringing in the correct values for this PO number now. However, of I use another PO Number 25020 instead of 24839 it is bringing in duplicate data again (see enclosed screen shot of data), I am only expecting 4 rows in this case. I have tried the joins and also started completely fresh adding in the supple invoice tables again, but keep getting 8, I got it doen to 6 but then the old PO 24839 went down to 3 lines instead of 5 lines. You mentioned adding a secondary join but did not understand this, could you advise further, many thanks

SELECT   dbo.PurchaseOrderHeader.PurchaseOrderNumber, dbo.PurchaseOrderHeader.TotalCostPrice, dbo.PurchaseOrderLine.TotalCostPrice AS TotalLineCostProce, 
                      dbo.PurchaseOrderHeader.OurRef, dbo.Supplier.Name, dbo.PurchaseOrderHeader.DateRequired, dbo.PurchaseOrderHeader.DateTimeCreated, 
                      dbo.Users.Name AS [Created By], dbo.StockReceiptLine.TotalCostPrice AS [GRN Value], dbo.StockReceiptHeader.DateReceived AS [GRN Date], 
                      dbo.StockReceiptHeader.StockReceiptNumber AS [GRN Ref], dbo.SupplierInvoiceHeader.DocumentNumber AS PinvNo, 
                      dbo.SupplierInvoiceHeader.DocumentDate AS PinvDate, dbo.SupplierInvoiceLine.InvoiceCostPrice AS PinvValue, 
                      dbo.SupplierInvoiceHeader.DateTimeCreated AS Datecreated, dbo.SupplierInvoiceHeader.DateTimeLastModified
FROM         dbo.PurchaseOrderHeader LEFT OUTER JOIN
                      dbo.Users ON dbo.PurchaseOrderHeader.UserID = dbo.Users.UserID LEFT OUTER JOIN
                      dbo.PurchaseOrderLine ON dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.PurchaseOrderLine.PurchaseOrderID INNER JOIN
                      dbo.StockReceiptLine ON dbo.PurchaseOrderLine.PurchaseOrderLineID = dbo.StockReceiptLine.PurchaseOrderLineID AND 
                      dbo.PurchaseOrderHeader.PurchaseOrderID = dbo.StockReceiptLine.PurchaseOrderID LEFT OUTER JOIN
                      dbo.SupplierInvoiceHeader INNER JOIN
                      dbo.SupplierInvoiceLine ON dbo.SupplierInvoiceHeader.DocumentID = dbo.SupplierInvoiceLine.DocumentID ON 
                      dbo.PurchaseOrderLine.PurchaseOrderLineID = dbo.SupplierInvoiceLine.PurchaseOrderLineID LEFT OUTER JOIN
                      dbo.Supplier ON dbo.PurchaseOrderHeader.SupplierID = dbo.Supplier.SupplierID FULL OUTER JOIN
                      dbo.StockReceiptHeader ON dbo.StockReceiptLine.StockReceiptID = dbo.StockReceiptHeader.StockReceiptID
WHERE     (dbo.PurchaseOrderHeader.PurchaseOrderNumber = 24839) AND (dbo.StockReceiptLine.TotalCostPrice <> 0)