SELECT DISTINCT
  CB.ClientBranchID,
  CONCAT('(', CB.ClientBranchID, ') ', CB.BranchName, ' - ', BR.BranchName) AS BranchName
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  (BP.Status = 1) AND
  (CB.BranchStatus = 1) AND
  (CP.Status = 1) AND
  (CB.ClientID = @nClientID) AND
  ((@nProductID = 0) OR (CP.ProductID = @nProductID))
