[MYSQL] New Companies / 계층구조의 외래키 설정

> 문제

 

회사의 계층구조

Given the table schemas below, write a query to print the 
company_code,  founder name,
total number of lead managers, / total number of senior managers,
total number of managers, and total number of employees

Order your output by ascending company_code

------------------------------
출력형식
: 회사코드 , founder 이름 ,  lead manager 수, senior manager 수, manager 수, employees 수

Note:
- The tables may contain duplicate records.T
- he company_code is string, so the sorting should not be numeric.
For example, if the company_codes are C_1, C_2, and C_10,
then the ascending company_codes will be C_1, C_10, and C_2.

 

 

>>Input Format

Company	Lead_Manager	Senior_Manager	Manager	Employee

 

[Company] : company_code, founder

[Lead_Manager] : lead_manager_code, company_code

[Senior_Manager] : senior_manager_code, lead_manager_code, company_code

[Manager] : manager_code ,senior_manager_code, lead_manager_code, company_code

[Employee] :employee_code, manager_code ,senior_manager_code, lead_manager_code, company_code

 

 

>>Sample Input

Company	Lead_Manger	Senior_Manager
Manager
Employee

 

>> Sample OutPut

-----------------------------------------

 

어려웠던 점

- 테이블이 다...다섯개?

- 중복된 행의 존재 -> Distinct

- 어떻게 JOIN 해야할지 감이 안잡혔음

 

 

>>JOIN에 대한 고민

선택1(빨간색) : COMPNAY_CODE로 공통 조인

선택2(파란색) : 각 테이블의 기본키를 외래키로 삼아 하나씩 JOIN

 

 

 

>>풀이1(빨간색)

 

 

>>풀이2(파란색)

 

>>OutPut