AS/400 Query Join Duplicate Records
I have three files A, B, C in my join query. Test is between A - B, B - C. The type is 2 (match records to primary file). When I run the query, it is returning duplicates. What am I doing wrong?
Software/Hardware used:
Software/Hardware used:
ASKED:
December 30, 2009 1:27 PM
UPDATED:
December 31, 2009 3:52 PM
Answer Wiki:
When you join two files and one or both of the files have multiple records for the join value, you will get multiple records in your output.
Example:
FIle A has 3 records. One with Key value of A and the other two with Key value of B,
File B has 5 records. Three records have the Key value of A and two records with the key value of B.
Here is the output:
FILE A - Key Value A with FILE B 1st Key Value of A
FILE A - Key Value A with FILE B 2nd Key Value of A
FILE A - Key Value A with FILE B 3rd Key Value of A
FILE A - 1sr Key Value B with FILE B 1st Key Value of B
FILE A - 1st Key Value B with FILE B 2nd Key Value of B
FILE A - 2nd Key Value of B with FILE B 1st Key Value of B
FILE A 2nd Key Value of B with FILE B 2nd Key Value of B
*
If you can include more elements in join, ot add Select/Omit Criteria, you may be able to reduce or eliminate the multiple records.
*
Else use SQL with SELECT DISTINCT
To see all answers submitted to the Answer Wiki: View Answer History.
CharlieBrowne has an appropriate example. To get some clear visual evidence with SQL, add a couple of additional fields — RRN(A) and RRN(B) — to the select list. The cause of duplicates should be clearer when you can see which records contribute the values.
Tom
When joining more than 2 files, I will often use multiple queries especially when duplicate records will be returned. For your example I would write 1 query to join A and B creating file D, then a second query joining D and C. With this exrta step, it is often easier to see where your duplicate records are coming from.