Sql Inner Join with distinct values

Question

I have 3 tables:

Bank Transfer
Beneficiaries
Callback_Procedure

I have to join those 3 tables with an inner join.

In the Bank Transfer, I have a column Number, and the Number is repeated in the Beneficiaries and Callback table.

In the Beneficiaries table, the number can repeat more than once.

When running my inner join query, am getting more than 1 result instead of getting unique values.

In the Beneficiaries table, I also have a column Beneficial_Name and it is this column that uniquely identifies the result, however when running the join query, am getting duplicate results.

I have tried distinct option, not to great result.

Below is my code - can someone please help me out?

SELECT DISTINCT   
    dbo.BT_Beneficiaries.Beneficial_Name,   
    dbo.Bank_Transfer.Number, dbo.Bank_Transfer.Start_Date, 
    dbo.Bank_Transfer.Team_Name, dbo.Bank_Transfer.Initiator, 
    dbo.Bank_Transfer.Staff, dbo.Bank_Transfer.Process_Name, 
    dbo.Bank_Transfer.Company_Name, dbo.Bank_Transfer.Billing_Amount, 
    dbo.Bank_Transfer.Entry_ID, dbo.Bank_Transfer.Instructions, 
    dbo.Bank_Transfer.BT_Count, dbo.Bank_Transfer.Misspelled, 
    dbo.Bank_Transfer.Sender, dbo.Bank_Transfer.Odd, 
    dbo.Bank_Transfer.Email, dbo.Bank_Transfer.Beneficiary, 
    dbo.Bank_Transfer.Address, dbo.Bank_Transfer.Urgent,
    dbo.Bank_Transfer.Payment, dbo.Bank_Transfer.Sperry, 
    dbo.Bank_Transfer.Contact, dbo.Bank_Transfer.Reasonable, 
    dbo.Bank_Transfer.Evidence_Completed, dbo.Bank_Transfer.End_Date, 
    dbo.Bank_Transfer.First_Mgr_Approval_Name, dbo.Bank_Transfer.First_Mgr_Approval, 
    dbo.Bank_Transfer.First_Mgr_Approval_Date, dbo.Bank_Transfer.CallBack_Workings, 
    dbo.Bank_Transfer.CallBack_Agreement, dbo.Bank_Transfer.CallBack_Procedure, 
    dbo.Bank_Transfer.Second_Mgr_Approval_Name, dbo.Bank_Transfer.Second_Mgr_Approval,
    dbo.Bank_Transfer.Second_Mgr_Approval_Date, dbo.Bank_Transfer.IB, 
    dbo.Bank_Transfer.IB_Date, dbo.Bank_Transfer.ITL_Signatory_Approval, 
    dbo.Bank_Transfer.ITL_Signatory_Name, 
    dbo.BT_Beneficiaries.Account_Number, dbo.BT_Beneficiaries.Currency, 
    dbo.BT_Beneficiaries.Payment_Details, dbo.BT_Beneficiaries.CurrencyPayment, 
    dbo.BT_Beneficiaries.Amount, dbo.BT_Beneficiaries.Purpose, 
    dbo.BT_Beneficiaries.Evidence, dbo.BT_Beneficiaries.Due_Diligence, 
    dbo.BT_Beneficiaries.WC, dbo.BT_Beneficiaries.Resolution_Directors,
    dbo.BT_Beneficiaries.Manager_Approval, dbo.BT_Beneficiaries.Mgr_Approval_Date, 
    dbo.CB_BankAccount.Number AS Expr3, dbo.CB_BankAccount.Company_Name AS Expr4, 
    dbo.CB_BankAccount.Bank_Name, dbo.CB_BankAccount.Bank_Account, 
    dbo.CB_BankAccount.Currency AS Expr5, dbo.CB_BankAccount.Balance, 
    dbo.CB_BankAccount.Bank_Mandate, dbo.CB_BankAccount.Signing_Arrangement AS Expr6,
    dbo.CB_CallBack_Procedure.Number AS Expr8, dbo.CB_CallBack_Procedure.CallStatus, 
    dbo.CB_CallBack_Procedure.Person_CalledBack, dbo.CB_CallBack_Procedure.DateTime,
    dbo.CB_CallBack_Procedure.Notes, dbo.CB_CallBack_Procedure.Mgr_Approval, 
    dbo.CB_CallBack_Procedure.DateTime_Approval, dbo.CB_CallBack_Procedure.Bank_DateTime 
FROM 
    dbo.Bank_Transfer 
INNER JOIN  
    dbo.BT_Beneficiaries ON dbo.Bank_Transfer.Number = dbo.BT_Beneficiaries.Number 
INNER JOIN 
    dbo.CB_BankAccount ON dbo.BT_Beneficiaries.Number = dbo.CB_BankAccount.BT_Number 
INNER JOIN 
    dbo.CB_CallBack_Procedure ON dbo.CB_BankAccount.BT_Number = dbo.CB_CallBack_Procedure.Number

What does it need to say in the Beneficial Name column for you to know the record is the one you want? — kjmerf

kjmerf kjmerf · Accepted Answer · 2017-10-15T01:15:26

Just add a WHERE clause that specifies what the Benficial_Name column needs to say to identify the unique record:

WHERE Beneficiaries = 'Unique Record'

Edit this to say whatever you need. You can put this after the joins.

Sql Inner Join with distinct values

1 Answers