两次使用同一列选择或联接

Question

所以我有三张桌子:雇员，秘书和经理

给定模式

Employee表有以下列：

• Employee_Number
• Name
• Home Address
• Telephone数

Secretary表包含：

• Secretary_Number
• Employee_Number (将外键链接到员工table)
• Manager_Number (与外键链接到Manager表)

Manager表包含：

• Manager_Number
• Employee_Number (将外键链接到Employee表)

什么是必需的，什么是我尝试的

我正在尝试做一个连接，这样我就可以看到以下列：

Number

• Secretary's Name

• Manager's Number

• Manager's
• 秘书的名字

我有以下join语句，它显示所有列，并显示秘书的姓名和编号以及Manager编号

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;

问题

但我不能让经理的名字出现，也不能重复秘书的名字。

任何帮助都将不胜感激！

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;