How to SELECT DISTINCT records with INNER JOIN?

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?