SELECT DISTINCT
  CB.ClientID,
  CB.ClientBranchID,
  CB.LabourNetBranchID,
  CB.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)
WHERE
  (TRIM(CB.MapReference) <> '') AND
  (CB.BranchStatus = 1) AND
  (BP.Status = 1) AND
  (BP.ConsultantID = @nConsultantID)
ORDER BY
  6 DESC