sql - SELECT 或 JOIN 使用同一列两次

Question

所以我有三个表：员工，秘书和经理

给定模式

该Employee表具有以下列：

• 员工编号
• 姓名
• 家庭地址
• 电话号码

该Secretary表包含：

• 秘书号码
• Employee_Number（用外键链接到 Employee 表）
• Manager_Number（与 Manager 表的外键链接）

该Manager表包含：

• 经理号码
• Employee_Number（用外键链接到 Employee 表）

需要什么以及我尝试了什么

我正在尝试进行 JOIN 以便可以看到以下列：

• 秘书号码
• 秘书姓名
• 经理编号
• 经理姓名

我有以下 join 语句，它显示了所有列，并显示了秘书的姓名和号码，以及经理号码：

SELECT
  SECRETARY.SECRETARY_NUMBER, 
  SECRETARY.EMPLOYEE_NUMBER AS SECRETARY_EMPLOYEE,
  EMPLOYEE.NAME AS SECRETARY_NAME,
  SECRETARY.MANAGER_NUMBER, 
  MANAGER.EMPLOYEE_NUMBER AS MANAGER_EMPLOYEE,
  EMPLOYEE.NAME AS MANAGER_NAME
FROM SECRETARY, MANAGER, EMPLOYEE
WHERE SECRETARY.MANAGER_NUMBER = MANAGER.MANAGER_NUMBER
AND SECRETARY.SECRETARY_NUMBER = EMPLOYEE.EMPLOYEE_NUMBER
AND MANAGER.EMPLOYEE_NUMBER = EMPLOYEE.EMPLOYEE_NUMBER;

问题

但是我无法显示Manager's Name ，或者不重复与 Secret Name相同的信息。

任何帮助将不胜感激！

标签： sqloracle11g

Answer 1

SELECT
  s.SECRETARY_NUMBER, 
  s.EMPLOYEE_NUMBER AS SECRETARY_EMPLOYEE,
  e.NAME AS SECRETARY_NAME,
  s.MANAGER_NUMBER, 
  m.EMPLOYEE_NUMBER AS MANAGER_EMPLOYEE,
  e2.NAME AS MANAGER_NAME
FROM 
  SECRETARY s
INNER JOIN
  EMPLOYEE e
ON
  e.EMPLOYEE_NUMBER = s.EMPLOYEE_NUMBER
INNER JOIN
  MANAGER m
ON
  m.EMPLOYEE_NUMBER = s.MANAGER_NUMBER
INNER JOIN
  EMPLOYEE e2
ON
 e2.EMPLOYEE_NUMBER = m.EMPLOYEE_NUMBER;