SELECT DISTINCT
  CB.ClientBranchID,
  CONCAT('(', CB.ClientBranchID, ') ', CB.BranchName, ' - ', BR.BranchName) AS BranchName
FROM
  taClientBranches AS CB
    LEFT JOIN taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  (CB.BranchStatus = 1) AND
  (CB.ClientID = @nClientID)
