Inner join returning duplicate records access sql

Question

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?

Subterranean

Answer 1

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 

Indigo

Result didn't change when using DISTINCT – Subterranean

@Subterranean What column is different between the rows? – Indigo

It is Suff column – Subterranean

@Subterranean Then I think perhaps your data is not like your example. Why would suff be different across tables? – Indigo

In the present table only difference is suffix but in the real i have more than 800 records, might be any of them different BNO or CSCode is different in some rows. – Subterranean

@Subterranean order the query by BNO-CSCcode AND suff, find your duplicates and see what other columns are different. – Indigo

Answer 2

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.

Gwen

Not Description field is diffirent. Suff is different. But that doesn't exists in Mapping table. – Subterranean

What do you mean by "Not Description field is different"? Do you mean to say that records in Mapp won't necessarily have the same Descriptions as in Things? – Gwen

Answer 3

    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

Whomp

Answer 4

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
   );

Bluebonnet