In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.

In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2, and another employee, E4, under manager, M3.

expected outcome:

C1 Monika 1 2 1 2

C2 Samantha 1 1 2 2

company table:

Lead_Manager table:

Senior_Manager table:

Manager table:

Employee table:

My code:

select count(company.company_code) from company
join lead_manager on company.company_code = lead_manager.company_code
join senior_manager on company.company_code = senior_manager.company_code
join manager on company.company_code = manager.company_code
join employee on company.company_code = employee.company_code
order by company.company_code;

current outup: 117637

The issue is in my first line. I don't know how what I should be

what i tried:

• select * from company

• select count(distinct lead_manager.company_code), count(distinct senior_manager.company_code), count(distinct manager.company_code), count(distinct employee.company_code) from company

CodePudding user response：

You should probably change your data model and use a single hierarchical table:

CREATE TABLE employees (
  id         NUMBER(8,0) PRIMARY KEY,
  company_id VARCHAR2(20),
  manager_id REFERENCES employees,
  name       VARCHAR2(20),
  job_title  VARCHAR2(20)
);

Then you can store the employees for C1 as:

INSERT INTO employees (id, company_id, manager_id, name, job_title)
SELECT 1, 'C1', NULL, 'Monica', 'Founder'        FROM DUAL UNION ALL
SELECT 2, 'C1', 1,    'Alice',  'Lead Manager'   FROM DUAL UNION ALL
SELECT 3, 'C1', 2,    'Betty',  'Senior Manager' FROM DUAL UNION ALL
SELECT 4, 'C1', 2,    'Carol',  'Senior Manager' FROM DUAL UNION ALL
SELECT 5, 'C1', 3,    'Debra',  'Manager'        FROM DUAL UNION ALL
SELECT 6, 'C1', 5,    'Emily',  'Employee'       FROM DUAL UNION ALL
SELECT 7, 'C1', 5,    'Fiona',  'Employee'       FROM DUAL;

and get your output using the hierarchical query:

SELECT company_id,
       MAX(CASE job_title WHEN 'Founder'        THEN name END) AS founder,
       COUNT(CASE job_title WHEN 'Lead Manager'   THEN id END) AS num_lead_manager,
       COUNT(CASE job_title WHEN 'Senior Manager' THEN id END) AS num_senior_manager,
       COUNT(CASE job_title WHEN 'Manager'        THEN id END) AS num_manager,
       COUNT(CASE job_title WHEN 'Employee'       THEN id END) AS num_employee
FROM   employees e
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id
GROUP BY company_id

Which outputs:

COMPANY_ID	FOUNDER	NUM_LEAD_MANAGER	NUM_SENIOR_MANAGER	NUM_MANAGER	NUM_EMPLOYEE
C1	Monica	1	2	1	2

db<>fiddle here