SELECT
  CB.LabourNetBranchID,
  BR.BranchName AS LabourNetBranchName,
  CB.ClientID,
  CS.GroupName,
  CB.ClientBranchID,
  CB.BranchName AS ClientBranchName,
  IF(CB.IsHeadOffice = 1, 'YES', 'no') AS IsHeadOffice,
  TRIM(CONCAT(CS.GroupName, ', ', TRIM(CB.Address1),' ',TRIM(CB.Address2),' ',TRIM(CB.Address3),' ',TRIM(CB.Address4))) AS SearchString
FROM
  taClientBranches AS CB
    LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  (CS.ClientStatus = 1) AND
  (CB.BranchStatus = 1) AND
  (TRIM(CB.MapReference) = '') AND
  (CONCAT(TRIM(CB.Address1),TRIM(CB.Address2),TRIM(CB.Address3)) <> '')
ORDER BY
  CB.ClientID,
  BR.BranchName,
  CB.BranchName
