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

It looks like there might be multiple similar values for FirstFourLastName and with join on FirstFourLastName you're getting every combination possible. For example:

FMAY table

LenderReference	FirstFourLastName
123	smit
456	smit

CCC Table

clientcode	FirstFourLastName
smit123	smit
smit456	smit

Omitting all other columns, the inner join on FirstFourLastName would produce:

LenderReference	clientcode	FirstFourLastName
123	smit123	smit
123	smit456	smit
456	smit123	smit
456	smit456	smit

I would double check that the clientcode is definitely attaching correctly. If LenderReference is unique, you could run distinct count of clientcodes against it.