We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. New Companies
  5. Discussions

New Companies

  • vitalijpimenov31
     + 0 comments

    select cmp.company_code, cmp.founder,

    (SELECT COUNT(DISTINCT LM.lead_manager_code) FROM Lead_Manager LM WHERE LM.company_code = cmp.company_code) as lead,

    (SELECT COUNT(DISTINCT SM.senior_manager_code) FROM Senior_Manager SM INNER JOIN Lead_Manager LM ON LM.lead_manager_code = SM.lead_manager_code WHERE LM.company_code = cmp.company_code) as senior_manager,

    (SELECT COUNT(DISTINCT M.manager_code) FROM Manager M INNER JOIN Senior_Manager SM ON SM.senior_manager_code = M.senior_manager_code INNER JOIN Lead_Manager LM ON LM.lead_manager_code = SM.lead_manager_code WHERE LM.company_code = cmp.company_code) as manager,

    (SELECT COUNT(DISTINCT E.employee_code) FROM Employee E INNER JOIN Manager M ON M.manager_code = E.manager_code INNER JOIN Senior_Manager SM ON SM.senior_manager_code = M.senior_manager_code INNER JOIN Lead_Manager LM ON LM.lead_manager_code = SM.lead_manager_code WHERE LM.company_code = cmp.company_code) as employee

    FROM Company as cmp GROUP By cmp.company_code, cmp.founder