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.
2) Your indices may be corrupt. Try reindexing.
3) Make sure you have SET DELETED ON, or else you may get unwanted records. For instance, you may have one record twice on the "left" table: one deleted, the other not.
HTH, Hilmar.
>Currently my boss ask me to create a new project on invoice checking
>between our Own Factory and our invoice.
>
>In current Database:
>Orders (PRO_INV_NO, PLAN_NO, INV_NO, ...)
>OrdItems (INV_NO, ITEM, QTY...)
>
>Print out to Factory:
>PLAN_NO, ITEM, QTY ...
>
>Where PLAN_NO is the P.O. of Factory.
>
>New project request to check out the factory invoice about the value correct
>or not including check QTY, Fact_cost.
>
>It seem simply, but really not.
>1) Partial Shipment:
>Current Database give 1+ INV_NO as invoice per shipment.
>but Factory invoice give
Same PLAN_NO, Same ITEM>
>2) HK Sales may not yet fill in INVOICE...
>3) Factory may miss item on invoice or same item ask for pay twice.
>
>To handle whoelse may not finish task, I need use
Full Join>
>4) HK Sales may fill up both shipment but Factory give 1st shipment invoice
>only. If both shipment involve same item#...
>
>Full Join cause duplicate record on factory parts..
>
>Sample result:
>
>HKinv_no PLAN_NO ITEM QTY | FacPLAN_NO FacInv_no FacITEM FacQTY
>
>6933 12345 MS-4421 20 12345 FT445 MS-4421 20 <~ ok
>6939 12345 MS-4421 80 12345 FT445 MS-4421 20 <~ not yet
>
>.NULL. .NULL. .NULL. .NULL. 12346 FT446 MS-4567 30 <~ notfill
>
>6935 12347 MS-4344 30 12347 FT447 MS-4344 30 <~ ok
>6935 12347 MS-4346 60 .NULL. .NULL. <~ Factory miss data
>
>6937 12348 MS-4354 38 12347 FT447 MS-4354 36 <~ ??
>
>
>
>3rd row show HK Sales not yet fill invoice, fail to match.
>5th row show Factory miss item not yet delivery, is it stock?
>
>First 2 rows make me headache.
>HK sales fill up both invoice information and retrieve them all.
>
>Another Similar case is 2nd shipment, take away HK data from matching by
>remove those record which already counted in last matching.
>That is ok although the data of FULL join give similar result as above..
>
>Now, if HK Sales fill up both, it come twice on Factory data..
>Of cos, there is only ONE row of qty 20, not TWO row.. (not double count)
>
>Original idea will desire the new project can check out any error on both staff work, such as last row, why the qty by factory is less 2 only...
>May final find out there was a little accidient and 2 items was broken..
>Thus, fail to delivery and need HK sales adjust the invoice correctly..
>
>Since both HK sales and Factory staff may fill data wrongly and duplicate,
>the checking process is hard..
>
>My Question is:
>
>How to cut off the 2nd row of above sample result after full join.
>Full join using PLAN_NO, ITEM as matching key because factory donno HK INV_NO#
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)