SELECT
  CS.ClientID,
  CS.ClientStatus,
  CS.TerminationDate AS ClientTerminationDate,
  CS.FinNumber,
  CS.GroupName,
  CS.IndustryID,
  CS.SubIndustryID,
  CS.SetaID,
  CS.ClientType,
  CS.CreditLimit,
  CS.PaymentTerms,
  CS.EstTurnOver,
  CS.DateJoined,
  SM.ClientGroup,
  CS.OrderCompulsory,
  CS.EmployersOrganisation,
  CS.ParentClientID,
  SM.ClientRetainer,
  SM.NoOfEmployees,
  CS.TerminationDate,
  CS.TerminationReason,
  CS.TerminationNotes,
  CS.SuspensionDate,
  CS.ReferredByClientID,
  CS.ContractTerm,
  CS.ContractNumber,
  CS.PaymentMethod,
  X.ChildCount,
  X.ChildFinNumbers
FROM
  taClients AS CS
    LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
    LEFT JOIN (
      SELECT ParentClientID AS ClientID, COUNT(*) AS ChildCount, GROUP_CONCAT(FinNumber SEPARATOR ', ') AS ChildFinNumbers
      FROM taClients
      WHERE (ParentClientID IS NOT NULL) AND (ParentClientID > 0)
      GROUP BY ParentClientID
    ) AS X ON (X.ClientID = CS.ClientID)
WHERE
  (CS.ClientID = @nClientID)
