SELECT DISTINCT
  CB.ClientID,
  CB.ClientBranchID,
  CB.LabourNetBranchID,
  CONCAT(CB.BranchName, ' (', BR.BranchName, ')') AS BranchName,
  CB.MapReference,
  CB.NoOfEmployees,
  fnClientBranchRetainer(CB.ClientBranchID, NOW()) AS BranchRetainer
FROM
  taClientBranches AS CB
    LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchID = CB.ClientBranchID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  (TRIM(CB.MapReference) <> '') AND
  (CB.ClientID = @nClientID)