使用同一列两次SELECT或JOIN

左轮手枪

所以我有三个表:员工,秘书和经理

给定架构

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;

问题

但是我无法显示经理的姓名,或者无法重复提供与秘书姓名相同的信息

任何帮助将不胜感激!

乔恩
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;

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章