join keeps cascading

  • Thread starter Thread starter mg
  • Start date Start date
M

mg

no idea what i'm doing wrong here.
my goal: keep all records from table-1, and append onto end of table-1 a
field from table-2....
current sql = select table1.*, table2.* from table1 left join table2 on
table1.code=table2.code

table1 has 3 records, table2 has 4 records
every combination of select or join i try, i always get 12 records
returned!!!
i even deleted the query, restarted access and tried again.

frustrated
thanks
 
Please describe the actual data a bit more...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
while i was waiting, i opened a new database and created similar tables to
try it again.
i noticed that the field i was joining on was not unique in table2.
i had assumed that the LEFT JOIN would keep my original records, and if
there was more than one match in table2 that it would attach fields from the
record that was FIRST or LAST matched.
that turns out to be a wrong assumption.
i have since tried to be sure the ON clause is using a set of fields that
would create a unique key. seems to work ok now.
BUT, is there any way to assure that no matter what happens, that i always
get all records from table1 returned, and no extra records added-in???
 
Back
Top