>>>You can get duplicates for several reasons.
>>>
>>>1) If one table has 3 copies on the matching field, and the other 4, you will get 3*4=12 duplicates in the result set.
>>>...
>>Yes, the 1) case.
>>
>>I want result is 1-by-1 matching, once match, skip it..
>>but both side data may missing...
>>
>>What should I do?
>>Thanks
>
>Two solutions:
>
>a) Eliminate the duplicates with a condition. For instance, if it is caused by empty records on both sides, add the clause WHERE NOT EMPTY(table1.KeyField) AND NOT EMPTY(table2.KeyField)
>
>b) Joins are often done on primary keys / foreign keys. Make sure the PK is really unique.
>
>Hilmar.

Hilmar, I know if it is Unique key or enough condition,
there will be no problem.

However, the real world problem is different.

By email, Factory Data only know:
P.O. # on Factory... (PLAN_NO)
ITEM #
Qty #
but Factory donno the corresponding invoice # on HK!

On the other hand, HK also donno the inv_no match to which factinv_no...


Since partial shipment on same P.O. of Factory(invoice per shipment),
HK side Invoice linked to same P.O. of Factory.

If the data handle by human, it can be very easy to find out which invoice is corresponding.

For example,

HKInv_no  PLAN_NO  ITEM  QTY  || FactInv_no PLAN_NO ITEM  QTY ...

(1st Shipment)
6944      12347    A-882 400     FINV3322   12347   A-882 400 <~ history(away)

(2nd Shipment)
6949      12347    A-882 400     FINV3344   12347   A-882 400 

(3rd Shipment not yet out)
6958      12347    A-882 400     FINV3344   12347   A-882 400 <~ dup. line

What I can do is to make a history reference,
6944 (1st shipment) is done and take away the record before join.

However, if HK Sales fill in invoice data on 3rd shipment but not yet shipped,
3rd invoice# 6958 also put out.

Although (FactInv_no + ITEM) is the PK of Factory data,
HK side donno which is corresponding.
Thus ask me write a prg to check it out..