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 taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  (CB.BranchStatus = 1) AND
  (BP.Status = 1) AND
  (CP.Status = 1) AND
  (TRIM(CB.MapReference) <> '') AND
  (PR.ProductCategory = @aProductCategory)
ORDER BY
  6 DESC