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

  • WilliFlemming
     + 1 comment

    I agree with you and @kimbaudi that matching on the manager and employee codes does not work if there are managers without subordinates and that it is necessary to match on company_code everywhere. I also believe that yours ist the most correct (if there is such a thing) solution when it comes to best practices.

    However since the question seems to be looking for a solution without joins, here is my solution using the implicit join via WHERE proposed by @karthiksk and @mialkin: 

    SELECT 
    c.company_code
    , c.founder
    , count(distinct l.lead_manager_code)
    , count(distinct s.senior_manager_code)
    , count(distinct m.manager_code)
    , count(distinct e.employee_code)
from Company c, Lead_Manager l, Senior_Manager s, Manager m, Employee e 
where c.company_code = l.company_code 
    and c.company_code = s.company_code
    and c.company_code = m.company_code
    and c.company_code = e.company_code
group by c.company_code, c.founder
ORDER BY c.company_code;