mysql - SQL 和外键的逻辑问题

Question

已编辑^*1

我是一名软件开发人员实习生，所以我对 MySQL 数据库没有太多经验。

我想为任务写一张票，它包含工作人员的详细信息以及需要哪个工作人员。

我在数据库中有一个表，其中包含 worker 的姓名和部门。

表worker如下所示:

+---------+---------+----------+------------+
| id (PK) | surname | forename | department |
+---------+---------+----------+------------+
| 1       | bar     | foo      | shop       |
+---------+---------+----------+------------+
| 2       | baz     | foo      | production |
...

表格tasks看起来像这样:

+---------+------------+----------+-------------+----------------+-------------------+----
| id (PK) | task       | shop_req | shop_worker | production_req | production_worker | ...
+---------+------------+----------+-------------+----------------+-------------------+----
| 1       | do sth.    | yes      | 1           | yes            | 2                 | ...
+---------+------------+----------+-------------+----------------+-------------------+----
| 2       | do sth.    | no       | (NULL)      | yes            | 2                 | ...
...

我该如何将它们连接在一起？

我尝试使用以下 SQL 代码:^*1

ALTER TABLE tasks

ADD CONSTRAINTS FK_tasks_shop
FOREIGN KEY (shop_worker) 
REFERENCES worker(id) 
ON UPDATE RESTRICT
ON DELETE RESTRICT,

ADD CONSTRAINTS FK_tasks_production
FOREIGN KEY (production_worker) 
REFERENCES worker(id) 
ON UPDATE RESTRICT
ON DELETE RESTRICT

当我使用以下 SQL 代码调用表tasks 时:

SELECT a.id, a.task, a.shop_req, CONCAT(b.surname, ', ', b.forename) AS shop_worker,
a.production_req, CONCAT(c.surname, ', ', c.forename) AS production_worker
FROM tasks AS a
JOIN worker AS b ON a.shop_worker = b.id
JOIN worker AS c ON a.production_worker = c.id

结果是:

+----+------+----------+-------------+----------------+-------------------+----
| id | task | shop_req | shop_worker | production_req | production_worker | ...
+----+------+----------+-------------+----------------+-------------------+----

这意味着结果只是列名，没有行。
我期望以下结果:

+----+------------+----------+-------------+----------------+-------------------+----
| id | task       | shop_req | shop_worker | production_req | production_worker | ...
+----+------------+----------+-------------+----------------+-------------------+----
| 1  | do sth.    | yes      | bar, foo    | yes            | baz, foo          | ...
+----+------------+----------+-------------+----------------+-------------------+----
| 2  | do sth.    | no       | (NULL)      | yes            | baz, foo          | ...
...

这里出了什么问题？我找不到错误。
有没有一种有效的方法来调用 SQL 代码？

非常感谢您的回答!

Answer 1

首先，您的查询缺少 tasks 的 AS a 别名，这应该会导致查询失败。添加后，您应该返回一行，但是...

由于您希望包含 tasks 表中的所有行，即使 shop_worker 或 product_worker 没有匹配的行，因此您将想要使用 left join 来代替，如下所示:

SELECT a.id, a.task, a.shop_req, CONCAT(b.surname, ', ', b.forename) AS shop_worker,
a.production_req, CONCAT(c.surname, ', ', c.forename) AS production_worker
FROM tasks AS a
LEFT JOIN worker AS b ON a.shop_worker = b.id
LEFT JOIN worker AS c ON a.production_worker = c.id

Sample SQL Fiddle

有关各种联接的详细介绍，请参阅:A Visual Explanation of SQL Joins