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

  • gabrielcardosof1
     + 0 comments

    I know there are simpler ways to solve this problem, but I want to figure it out using a non-correlated subquery, because I've just studied it.

    My solution in MySQL:

    SELECT
    c.company_code,
    c.founder,
    (SELECT COUNT(DISTINCT lead_manager_code) FROM lead_manager l WHERE l.company_code = c.company_code) AS total_lead_managers,
    (SELECT COUNT(DISTINCT senior_manager_code) FROM senior_manager s WHERE s.company_code = c.company_code) AS total_senior_managers,
    (SELECT COUNT(DISTINCT manager_code) FROM manager m WHERE m.company_code = c.company_code) AS total_managers,
    (SELECT COUNT(DISTINCT employee_code) FROM employee e WHERE e.company_code = c.company_code) AS total_employee
FROM company c 
ORDER BY c.company_code ASC