How to SELECT DISTINCT records with INNER JOIN?

Question

I have two tables Finance Commissions May2021 (FMAY) and Consolidated Client Codes (CCC).

Here is the code for Insert Into: For table FMAY

 USE [FinanceCommissions26May2021]
GO

/****** Object:  Table [dbo].[Finance_Commissions_May26_2021$]    Script Date: 22/06/2021 11:06:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Finance_Commissions_May26_2021$](
    [Lender Reference] [nvarchar](255) NULL,
    [F2] [nvarchar](255) NULL,
    [F3] [nvarchar](255) NULL,
    [Client] [nvarchar](255) NULL,
    [F5] [nvarchar](255) NULL,
    [Supplier] [nvarchar](255) NULL,
    [Product] [nvarchar](255) NULL,
    [Settlement / Inception Date] [nvarchar](255) NULL,
    [Base Value] [nvarchar](255) NULL,
    [Original Loan Amount] [money] NULL,
    [Adviser Split (%)*] [money] NULL,
    [F12] [nvarchar](255) NULL,
    [Supplier_Amount_(incl GST)] [money] NULL,
    [Supplier_Amount_(GST)] [money] NULL,
    [Adviser Share (ex GST)] [money] NULL,
    [F16] [nvarchar](255) NULL,
    [F17] [nvarchar](255) NULL,
    [Adviser Share (GST)] [money] NULL,
    [Adviser Share (incl GST)] [money] NULL,
    [LastNameOnly] [nvarchar](255) NULL,
    [FirstNameOnly] [nvarchar](255) NULL,
    [FirstFourLastName] [nvarchar](255) NULL,
    [ClientCode] [nvarchar](255) NULL,
    [IndexMatch] [nvarchar](255) NULL,
    [F25] [nvarchar](255) NULL,
    [F26] [float] NULL
) ON [PRIMARY]
GO

FMAY

Table FMAY contains the following columns:

• commission amounts
• a Lender Reference Number
• client names (LastName, FirstName).

This report comes in monthly and contains monthly commissions for some of the clients (usually contains 350-400 records).

CREATE TABLE [Finance_Commissions_May26_2021$] (
    CommissionAmount,
    LenderReferenceNumber,
    ClientLastName,
    ClientFirstName
)

& here is the code for table CCC:

USE [FinanceCommissions26May2021]
GO

/****** Object:  Table [dbo].[Consolidated_ClientCodes$]    Script Date: 22/06/2021 11:07:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Consolidated_ClientCodes$](
    [FirstFourLastName] [nvarchar](255) NULL,
    [ClientCode] [nvarchar](255) NULL,
    [Client] [nvarchar](255) NULL
) ON [PRIMARY]
GO

CCC

Table CCC contains the following columns:

• client names
  • (different format though - i.e. FirstName,LastName)
• a client code (which is a unique identifier (Primary key) assigned to each client.
  • Client code takes the form of SMIT123.
  • e.g. for "John, Smith" it's SMIT123
  • for "Mark, Smith" it's SMIT345, and so on.
  • i.e. First Four letters of last name followed by a unique 3 digit code.

This table contains Client names and unique Client codes for ALL clients (more than 7000 records)

CREATE TABLE ['20210617145928-Exception - No C$'] (
    ClientCode PRIMARY KEY, -- '\w\w\w\w\d\d\d'
    FirstName,
    LastName
)

The problem

My aim is : To assign this unique Client code to each client within the FMAY table.

Since there was NO common column between tables FMAY and CCC, I created a new column (FirstFourLastName) which parses out FirstFour letters of Last Name (like "SMIT") from both Tables.

Then using this new column (FirstFourLastName), I wrote code for Inner Join in SQL with an aim to assign the Unique "Client Code" to each record in table FMAY.

I am able to get the correct client code assigned but not able to ONLY select distinct records.

My code is:

select
    FMAY.[Lender Reference],
    CCC.Client,
    CCC.ClientCode,
    FMAY.FirstFourLastName,
    FMAY.[Adviser Share (ex GST)],
    FMAY.[Adviser Share (GST)],
    FMAY.[Adviser Share (incl GST)],
    FMAY.Product 
from
    [FinanceCommissions26May2021].[dbo].[Finance_Commissions_May26_2021$] FMAY
    inner join [FinanceCommissions26May2021].[dbo].['20210617145928-Exception - No C$'] CCC on
        FMAY.FirstFourLastName = CCC.FirstFourLastName

The above code is giving me 6300 records, with the correct client code attached to each record though! However, I only need to assign Client Code to the 350-400 records in the FMAY table.

How can I select distinct records for my problem please?

Answer 1

You likely have a many-to-many join going on between the two tables, as the same value of the FirstFourLastName column must exist multiple times in both of the tables, if you're getting unexpected duplicates.

In that case, you either need to find a more unique way to relate the two tables or you can just use the DISTINCT keyword to remove true duplicates like this:

select DISTINCT
    FMAY.[Lender Reference],
    CCC.Client,
    CCC.ClientCode,
    FMAY.FirstFourLastName,
    FMAY.[Adviser Share (ex GST)],
    FMAY.[Adviser Share (GST)],
    FMAY.[Adviser Share (incl GST)],
    FMAY.Product 
from
    [FinanceCommissions26May2021].[dbo].[Finance_Commissions_May26_2021$] FMAY
    inner join [FinanceCommissions26May2021].[dbo].['20210617145928-Exception - No C$'] CCC on 
        FMAY.FirstFourLastName = CCC.FirstFourLastName