- Prepare
- SQL
- Advanced Select
- New Companies
- Discussions
New Companies
New Companies
+ 0 comments SELECT c.company_code, SUBSTRING(c.company_code,2,CHARINDEX('_',c.company_code)) AS com_code , c.founder, COUNT(DISTINCT(lm.lead_manager_code)), COUNT(DISTINCT(sm.senior_manager_code)), COUNT(DISTINCT(m.manager_code)), COUNT(DISTINCT(e.employee_code)) FROM Company c LEFT JOIN Lead_Manager lm ON c.company_code = lm.company_code LEFT JOIN Senior_Manager sm ON c.company_code = sm.company_code LEFT JOIN Manager m ON c.company_code = m.company_code LEFT JOIN Employee e ON c.company_code = m.company_code ORDER BY com_code ASC;
+ 0 comments SELECT Company.company_code, Company.founder, COUNT(DISTINCT Lead_Manager.lead_manager_code), COUNT(DISTINCT Senior_Manager.senior_manager_code), COUNT(DISTINCT Manager.manager_code), COUNT(DISTINCT Employee.employee_code) FROM Company INNER JOIN Lead_Manager ON Company.company_code = Lead_Manager.company_code INNER JOIN Senior_Manager ON Senior_Manager.company_code= Lead_Manager.company_code INNER JOIN Manager ON Manager.company_code = Lead_Manager.company_code INNER JOIN Employee ON Employee.company_code = Manager.company_code GROUP BY Company.company_code, Company.founder ORDER BY Company.company_code;
+ 0 comments On oracle i notice the company_code is repeated on all tables so i made join on it with all tables
select A.company_code , A.founder , count(distinct lm.lead_manager_code) , count(distinct sm.senior_manager_code) , count(distinct m.manager_code) , count(distinct e.employee_code) from company A join Lead_Manager lm on A.company_code =lm.company_code join Senior_Manager sm on A.company_code =sm.company_code join Manager m on A.company_code = m.company_code join Employee e on A.company_code = e.company_code group by A.company_code,A.founder order by A.company_code;
+ 0 comments MySQL
select A.company_code , A.founder , (select count(distinct lead_manager_code) from lead_manager where A.company_code = company_code) , (select count(distinct senior_manager_code) from senior_manager where A.company_code = company_code) , (select count(distinct manager_code) from manager where A.company_code = company_code) , (select count(distinct employee_code) from employee where A.company_code = company_code) from company as A order by company_code;
+ 0 comments Solution without using joins :
select company_code as c,founder , (select count(distinct lead_manager_code) from Lead_Manager where company_code=c), (select count(distinct senior_manager_code) from Senior_Manager where company_code=c), (select count(distinct manager_code) from Manager where company_code=c), (select count(distinct employee_code) from Employee where company_code=c) from Company order by company_code
Sort 2111 Discussions, By:
Please Login in order to post a comment