SELECT DISTINCT
  CB.ClientID,
  CB.ClientBranchID,
  CB.LabourNetBranchID,
  CONCAT(CB.BranchName, IF(BR.BranchID IS NULL, ' (no LabourNet branch)', CONCAT(' (', BR.BranchName, ')'))) AS BranchName,
  CB.MapReference,
  CB.Address1,
  CB.Address2,
  CB.Address3,
  CB.Address4,
  CB.Address5
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 taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  (TRIM(CB.MapReference) <> '')
  AND ((@nClientStatus < 0) OR (@nClientStatus = CB.BranchStatus))
  AND ((@nClientStatus < 0) OR (@nClientStatus = BP.Status))
  AND ((@nClientStatus < 0) OR (@nClientStatus = CP.Status))

  @pProductID
  @pBranchID
  @pClientID
  @pConsultantID
