Joins pulling too many records

[#1]

The main table in my project is T_BDE_DAILY.  Based on the logic it should pull in 2900 records.  I executed a join with a flat file called t_ref_msa_zips which has 80,000 records.  Here is the code snippet:

T_REF_MSA_ZIPS_Temp:
LOAD 
zip as [Prop Zip Code],
CountyFIPS as [County FIPS Code],
COUNTYNM as  CountyName
FROM  $(base_path)Mortgage\Data\Text\t_ref_msa_zips.txt(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
;

Right join
Load
Distinct [Loan Number],
[Property Zip Code]
Resident T_BDE_DAILY;


T_REF_MSA_ZIPS:
NoConcatenate
Load
[Loan Number],
[Property Zip Code],
[Prop Zip Code],
[County FIPS Code],
CountyName
Resident  T_REF_MSA_ZIPS_Temp
Where [Property Zip Code]= [Prop Zip Code];

Drop Table T_REF_MSA_ZIPS_Temp;

I joined the T_REF_MSA_ZIPS table to T_BDE_DAILY using a right join.  I then dropped the temp table in an effort to merge the T_REF_MSA_ZIPS to the T_BDE_DAILY.  Now instead of 2900 I get 224,000 records so Its double counting.  I am also getting an error as the project says it cannot find the field CountyName.  I am troubleshooting here for suggestions.

[marcus_sommer]

You need a (or several) key-field(s) to merge tables with a join. I could imagine that you need something like this:

T_REF_MSA_ZIPS:
LOAD 
zip as [Property Zip Code],
CountyFIPS as [County FIPS Code],
COUNTYNM as  CountyName
FROM  $(base_path)Mortgage\Data\Text\t_ref_msa_zips.txt(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
;

Right join
Load
Distinct [Loan Number],
[Property Zip Code]
Resident T_BDE_DAILY;

- Marcus

[effinty2112]

Hi Jerome,

                    Try this. This sequence seems to be to be a more natural way of doing thing (at least to me) and easier to follow.

T_REF_MSA_ZIPS_Temp:

Load

Distinct [Loan Number],

[Property Zip Code] as [Prop Zip Code]

Resident T_BDE_DAILY;

Left Join(T_REF_MSA_ZIPS_Temp)

LOAD 

zip as [Prop Zip Code],

CountyFIPS as [County FIPS Code],

COUNTYNM as  CountyName

FROM  $(base_path)Mortgage\Data\Text\t_ref_msa_zips.txt(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

T_REF_MSA_ZIPS:

NoConcatenate

Load

[Loan Number],

[Prop Zip Code] as [Property Zip Code],

[Prop Zip Code],

[County FIPS Code],

CountyName

Resident  T_REF_MSA_ZIPS_Temp;

Drop Table T_REF_MSA_ZIPS_Temp;

Cheers

Andrew