INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with Joins

Help with Joins

(OP)
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


CODE --> sql

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 

RE: Help with Joins

If you post some example data that would be easier.

Borislav Borissov
VFP9 SP2, SQL Server

RE: Help with Joins

(OP)
Hi

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



RE: Help with Joins

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

Bye, Olaf.

RE: Help with Joins

(OP)
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

RE: Help with Joins

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 AND dbo.PurchaseOrderHeader.somecolumn = dbo.SupplierInvoiceHeader.somecolumn

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:

CODE

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.

RE: Help with Joins

(OP)
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

CODE --> sql

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) 


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close