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

[former_member807870]

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.

[DominikTylczyn]

Hello

Have a look at DISTINCT SQL addition. Try something like that:

select DISTINCT 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

Best regards

Dominik Tylczynski