Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Join table cause unwanted duplicate
Message
From
08/01/2002 06:24:08
Hilmar Zonneveld
Independent Consultant
Cochabamba, Bolivia
 
 
To
08/01/2002 03:27:23
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00601834
Message ID:
00601861
Views:
21
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)
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform