Inner join returning duplicate records access sql
BNO-CSCcode
contains duplicates. You are joining the first record of Things
to both records of Mapp
, then the second record of Things
joins to both records of Mapp
. Giving you a total of 4 records.
If you want to join these together, you need some unique way of identifying the rows between the tables.
A Distinct should bring it back down to 2 records, but likely you need to join on a few more fields to get it to 2 records:
SELECT DISTINCT
Things.Prefix,
Things.Baseletter,
Things.suff,
Things.CSCcode,
Things.WT,
Mapping.BNO-CSCcode,
Mapping.EID
FROM
Things
INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode
You are getting duplicates because both records in the Things
table have a BNO-CSCcode
of 5560-04020, as do both records in the Mapp
table. The only distinction between the two that I can see is the Description
field. So, if you use the following query:
SELECT Things.Prefix ,Things.Baseletter,Things.suff,Things.CSCcode,Things.WT,Mapping.BNO-CSCcode,Mapping.EID
FROM Things INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode AND
Things.Description = Mapping.Description
...you should eliminate the duplication.
However, using a field that is obviously a text field in a join is not best practice, as if they are separately user-generated, they are unlikely to match, and it takes longer (relatively speaking) to match fields with a higher byte-count than those with low byte-counts.