>>>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..
The weak wait for chance, The strong bid for chance,
The clever notch up chance, but The merciful give you chance.