CODEWITHSUNDEEP
sqlms-access
vuyy1182
vuyy1182

Reputation: 1696

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?

Upvotes: 21

Views: 118933

Answers (4)

Fazeel Mehar
Fazeel Mehar

Reputation: 61

    select
       A.Code,
       B.Code as CustomerBeneficiaryCode,
       C.AccountNo as  CustomerBeneficiaryBankAccountNo
       ,COUNT(*) as Duplicate

from Table A
inner join  Table B on A.id = B.CustomerIndividualId
inner join  Table C on B.Id = C.CustomerBeneficiaryId

group by C.AccountNo,B.Code,A.Code having COUNT(*) > 1

Upvotes: 2

crthompson
crthompson

Reputation: 15875

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

Upvotes: 33

Simon
Simon

Reputation: 1

Try This

SELECT DISTINCT Prefix,
            Baseletter,
            suff,
            CSCcode,
            WT,
            BNO_CSCcode,
            EID
FROM (
       SELECT Things.Prefix AS Prefix,
              Things.Baseletter AS Baseletter,
              Things.suff AS suff,
              Things.CSCcode AS CSCcode,
              Things.WT AS WT,
              Mapping.BNO - CSCcode AS BNO_CSCcode,
              Mapping.EID AS EID
         FROM Things
              INNER JOIN
              Mapping ON Things.BNO - CSCcode = Mapping.BNO - CSCcode
   );

Upvotes: 0

Monty Wild
Monty Wild

Reputation: 4001

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.

Upvotes: 4

Related Questions