boldleft join two tables 1

[lyudmila]

Hello all,
I do

"SELECT INTO" some fields from two tables, which have three key fields, using syntax "TBL1 left outer join TBL2 on TBL1.f1=TBL2.f1 and TBL1.f2=TBL2.f2 and TBL1.f3=TBL2.f3".

TBL1 has 2,800,000. I thought my new created table should have same number of records as my driving table 2,800,000. Isn't? My table grew much larger and I cannot find the reason. Plus it has 7 dups. I found reason for dups (i do not select field that has batch number). But my table much larger than this 7 dups. Any idea? Thank in advance.

 

[MeanGreen]

My guess is that you have duplicate f1,f2,and f3 in Table2. For each duplicate match, you will get double rows in your output. Example:

Table 1
columns f1,f2,f3
a,b,c
d,e,f
g,h,i

Table 2
columns f1,f2,f3
a,b,c
a,b,c
a,b,c
g,h,i

select * from table1 a1 left join table2 a2 on etc...
will get you:

a1.a,a1.b,a1.c,a2.a,a2.b,a2.c
a1.a,a1.b,a1.c,a2.a,a2.b,a2.c
a1.a,a1.b,a1.c,a2.a,a2.b,a2.c
a1.d,a1.e,a1.f,null,null,null
a1.g,a1.h,a1.i,a2.g,a2.h,a2.i

TABLE 1 has 3 rows but your join will produce 5 rows.

Hope this helps.

 

[lyudmila]

thank you! i was sure I havw checked key fields for dups! But i did not!