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

Sort 2111 Discussions, By:

  • wakudkar_shubha1
     + 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;

  • vipimango
     + 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;

  • abdelrrahmanmoh1
     + 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;

  • h20181305
     + 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;

  • vibhawpriansh98
     + 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

Load more conversations