• + 0 comments

    I have used inner joins because I think the select query is more complicate and hard to read.

    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 INNER JOIN Lead_Manager L ON L.company_code=C.company_code INNER JOIN Senior_Manager S ON S.company_code=C.company_code AND S.lead_manager_code=L.lead_manager_code INNER JOIN Manager M ON M.company_code=C.company_code AND M.senior_manager_code=S.senior_manager_code INNER JOIN Employee E ON E.company_code=C.company_code AND E.manager_code=M.manager_code group by c.company_code, c.founder order by c.company_code