[SOLVED] Inner join returning duplicate records access sql

Inner join returning duplicate records access sql

I have the below table. the only difference in data is suff, wt; the rest of them look the same.

Things table

  Prefix  Baseletter  suff   CSCcode   Description  WT      BNO      BNO-CSCcode 

  EIGC    A5560        BGA    04020    blah1        0       5560      5560-04020
  EIGC    A5560        HEA    04020    blah2        17.9    5560      5560-04020

Mapp table

  BNO      BNO-CSCcode    EID    Description   

  5560      5560-04020   4005    blah1
  5560      5560-04020   4011    blah2

I'm trying to inner join them using BNO-CSCcode to get EID for corresponding BNO. But my query is returning duplicates. I'm getting 4 records, even though the first table only has two records.

My SQL 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

Why am I getting these duplicates, and how can I fix that?

Solution

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