mysql - SQL查询多表问题

Question

我有 3 个表，子表、员工表和部门，其中包含一些示例数据。我需要获取:

1. child 的名字
2. 相应员工的名字和姓氏
3. 该员工经理的名字和姓氏。

我想根据上述标准分隔列中的数据。

子表:

+-----------+-------+
| empld_id  | name  |
+-----------+-------+
| 123456789 | Manny |
| 333445555 | Joy   |
| 987654321 | abner |
+-----------+-------+

员工表:

+-------+-------+-----------+-----+-----------+--------+
| fname | lname |    ssn    | sex | superssn  | deptNo |
+-------+-------+-----------+-----+-----------+--------+
| john  | smith | 123456789 | m   | 333445555 |      5 |
+-------+-------+-----------+-----+-----------+--------+

部门表:

+-----------------+--------+------------+
| department_name | deptNo | managerssn |
+-----------------+--------+------------+
| HQ              |      1 | 8888665555 |
| admin           |      4 | 9876543211 |
+-----------------+--------+------------+

我目前有这个查询:

select 
  A.name,
  B.fname, 
  B.lname 
from 
  child A, 
  employee B, 
  department C
where 
  A.empld_id= B.ssn and 
  C.managerssn = B.ssn;

Answer 1

您几乎完成了 - 您拥有经理的 SSN - 现在您需要做的就是将其连接回员工表以获取他的详细信息。

要继续您的查询样式:

SELECT c.name, 
       e.fname, e.lname, 
       m.fname AS manager_fname, m.lname AS manager_lname
FROM   child c, employee e, department d, employee m
WHERE  c.empid_id = e.ssn AND d.managerssn = e.ssn AND m.ssn = d.managerssn

但是请注意，隐式连接(在 from 子句中有多个表)已经被弃用了很长一段时间，建议避免使用它们并使用显式 joins 代替:

SELECT c.name, 
       e.fname, e.lname, 
       m.fname AS manager_fname, m.lname AS manager_lname
FROM   child c
JOIN   employee e ON c.empid_id = e.ssn
JOIN   department d ON d.managerssn = e.ssn
JOIN   employee m ON m.ssn = d.managerssn