SELECT

  -- Client Details
  CS.ClientID,
  CONCAT('(',CS.FinNumber,') ', CS.GroupName) AS SubjectName,
  SM.ClientName,
  CS.FinNumber,
  SM.BranchCount,
  SM.NoOfEmployees,
  SM.ClientRetainer,
  SM.AccountBalance,
  SM.OverdueBalance,

  -- Branch Manager
  BR.BranchManagerID,

  -- Regional Product Managers
  ( SELECT GROUP_CONCAT(DISTINCT BS.ConsultantID)
    FROM
        taClientBranchProducts AS BP
          LEFT JOIN taClientProducts  AS CP ON (CP.ClientProductID = BP.ClientProductID)
          LEFT JOIN taProducts        AS PR ON (PR.ProductID       = CP.ProductID),
        taBranchStructure AS BS
    WHERE
      (BS.Description LIKE CONCAT('%Regional ', PR.ProductCategory, ' Manager%')) AND
      (BP.Status = 1) AND
      (BS.BranchID = HO.LabourNetBranchID) AND
      (CP.ClientID = CS.ClientID)
  ) AS RegionalProductManagerID,

  -- Product Executives
  ( SELECT GROUP_CONCAT(DISTINCT PC.ProductManagerID)
    FROM
      taClientBranchProducts AS BP
        LEFT JOIN taClientProducts  AS CP ON (CP.ClientProductID = BP.ClientProductID)
        LEFT JOIN taProducts        AS PR ON (PR.ProductID       = CP.ProductID)
        LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    WHERE
      (PC.ProductManagerID IS NOT NULL) AND
      (PC.ProductManagerID > 0) AND
      (BP.Status = 1) AND
      (CP.ClientID = CS.ClientID)
  ) AS ProductExecutiveID,

  -- Allocated Consultants
  ( SELECT GROUP_CONCAT(DISTINCT BP.ConsultantID)
    FROM
      taClientBranchProducts AS BP
        LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    WHERE
      (BP.ConsultantID IS NOT NULL) AND
      (BP.ConsultantID > 0) AND
      (BP.Status = 1) AND
      (CP.ClientID = CS.ClientID)
  ) AS ConsultantID,

  -- Sales People
  ( SELECT GROUP_CONCAT(DISTINCT BP.SalesCloserID)
    FROM
      taClientBranchProducts AS BP
        LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    WHERE
      (BP.ConsultantID IS NOT NULL) AND
      (BP.ConsultantID > 0) AND
      (BP.Status = 1) AND
      (CP.ClientID = CS.ClientID)
  ) AS SalesPersonID,

  -- Branch Administrators
  ( SELECT GROUP_CONCAT(DISTINCT ConsultantID)
    FROM taBranchStructure
    WHERE
      (BranchID = HO.LabourNetBranchID) AND
      (Description LIKE '%Branch Administrator%')
  ) AS BranchAdministratorID,

  -- Debtor Clerk
  FC.DebtorClerkID
FROM
  taClients AS CS
    LEFT JOIN taClientSummary  AS SM ON (SM.ClientID      = CS.ClientID)
    LEFT JOIN taFinCategory    AS FC ON (FC.FinCategoryID = CS.FinCategory)
    LEFT JOIN taClientBranches AS HO ON ((HO.ClientID = CS.ClientID) AND (HO.IsHeadOffice = 1) AND (HO.BranchStatus = CS.ClientStatus))
    LEFT JOIN taBranches       AS BR ON (BR.BranchID      = HO.LabourNetBranchID)
WHERE
  (CS.ClientID = @nClientID)