SELECT TOP 50 left(A.AccountManager,1) + right(A.AccountManager,charindex(' ',reverse(A.AccountManager))-1) as AccountManager, CampaignDescription as potentialname, A.ClientName, B.ClientTelephone, A.ClientEmail, BusinessType, ContactFirstName, ContactSurname FROM HBT.dbo.Client AS A
RIGHT JOIN (SELECT DISTINCT ClientTelephone FROM HBT.dbo.Client) AS B
ON A.ClientTelephone = B.ClientTelephone
INNER JOIN HBT.dbo.TRACK_CampaignClient ON
A.ClientID = HBT.dbo.TRACK_CampaignClient.ClientID
INNER JOIN HBT.dbo.TRACK_Campaign ON
hbt.dbo.TRACK_Campaign.CampaignID = HBT.dbo.TRACK_CampaignClient.CampaignID
INNER JOIN HBT.dbo.TRACK_ClientExistingSystem ON
A.ClientID = HBT.dbo.TRACK_ClientExistingSystem.ClientID
INNER JOIN HBT.dbo.ClientContact ON
A.ClientID = HBT.dbo.ClientContact.ContactClientID
WHERE A.AccountManager = 'person name here'