Skip to Content
0
Jun 09, 2022 at 09:23 AM

Joining two different tables with no duplicates - PO, GR and Invoice

111 Views Last edit Jun 09, 2022 at 09:26 AM 3 rev

I am using two different table to get the GR and Invoice for a PO Number.

I am using I_MaterialDocumentRecord to get the GR, and it has the following data:

PurchaseOrder    | MaterialDocument
4500003059 | 5000000353
4500003059 | 5000000354

And for the Invoice I am using I_PurchaseOrderHistoryAPI01, and it has the following data:
PurchaseOrder    | Invoice
4500003059 | 5105600387
4500003059 | 5105600388

This is my syntax to join them:

select a~PURCHASEORDER, a~MaterialDocument, B~PurchasingHistoryDocument
FROM I_MaterialDocumentRecord AS A<br>INNER JOIN I_PurchaseOrderHistoryAPI01 AS B ON A~PURCHASEORDER = B~PURCHASEORDER
WHERE A~PURCHASEORDER = 4500003059


But when I try to use this syntax, the Material Document and Invoice is Duplicated:

PurchaseOrder    | Invoice      | MaterialDocument
4500003059 | 5105600387 | 5000000353
4500003059 | 5105600387 | 5000000354
4500003059 | 5105600388 | 5000000353
4500003059 | 5105600388 | 5000000354

My expected output should just be:

PurchaseOrder    | Invoice      | MaterialDocument
4500003059 | 5105600387 | 5000000353
4500003059 | 5105600388 | 5000000354

The goal is to use just one SQL syntax for this to be achieved.