使用 INNER JOIN 的 MySQL 查询，没有返回我期望的内容

Question

我在执行以下查询时遇到问题:

SELECT costingjobtimes.TimeStamp, costingdepartment.DeptDesc,  costingemployee.Name, costingjobtimes.TimeElapsed FROM costingemployee INNER JOIN (costingdepartment INNER JOIN costingjobtimes ON costingdepartment.DeptID = costingjobtimes.DeptID) ON costingemployee.EmployeeID = costingjobtimes.EmployeeID;

我希望它返回 costingjobtimes 数据库中的每一行，但目前只返回 4 行。

基本上，我有 3 张 table 。 (costingjobtimes、costingdepartment、costingemployee)如下:

mysql> DESCRIBE costingemployee
    -> ;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| EmployeeID | varchar(8) |      | PRI |         |       |
| Name       | text       |      |     |         |       |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DESCRIBE costingdepartment
    -> ;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| DeptID   | int(10) unsigned |      | PRI | 0       |       |
| DeptDesc | text             |      |     |         |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DESCRIBE costingjobtimes
    -> ;
+-------------+------------------+------+-----+---------------------+-----------
-----+
| Field       | Type             | Null | Key | Default             | Extra
     |
+-------------+------------------+------+-----+---------------------+-----------
-----+
| id          | int(10) unsigned |      | PRI | NULL                | auto_incre
ment |
| EmployeeID  | text             |      |     |                     |
     |
| DeptID      | int(10) unsigned |      |     | 0                   |
     |
| JobNumber   | text             |      |     |                     |
     |
| TimeElapsed | decimal(10,5)    |      |     | 0.00000             |
     |
| TimeStamp   | datetime         |      |     | 0000-00-00 00:00:00 |
     |
| JobRef      | text             |      |     |                     |
     |
+-------------+------------------+------+-----+---------------------+-----------
-----+
7 rows in set (0.00 sec)

因此，所有查询应该做的就是返回 costingjobtimes 中的所有行，但输入员工姓名而不是 EmployeeID，输入部门描述而不是 DeptID。任何帮助都会很棒..

提前致谢，

Answer 1

我不知道 Inner Join 是否真的是您正在寻找的 teishu。也许您应该尝试正常的连接。

例如:

select e.Name, d.DeptDesc, j.JobNumber, j.TimeElapsed, j.TimeStamp, j.JobRef
  from costingjobtimes as j
  join costingdepartment as d on d.DeptID = j.DeptId
  join costingemployee as e on e.EmployeeID = j.EmployeeID

这将为您提供一个结果集，其中包含 costingjobtimes 表中的信息以及员工姓名和部门描述。

希望有帮助。