GROUP_CONCT和JOIN与多个查找表的关系

Question

我有一系列带有GROUP_CONCAT的简单联接，这些联接需要对多个表和查找表执行。

Table unit:
+---------+-----------+
| unit_pk | unit_name |
+---------+-----------+
Table unit_outcome:
+----------------+--------------+
|unit_outcome_pk | unit_outcome |
+----------------+--------------+

Table discipline_outcome:
+----------------------+--------------------+
|discipline_outcome_pk | discipline_outcome |
+----------------------+--------------------+

Table unit_outcome_discipline_outcome_lookup:
+-------------------------------------------+-----------------+-----------------------+
| unit_outcome_discipline_outcome_lookup_pk | unit_outcome_fk | discipline_outcome_fk |
+-------------------------------------------+-----------------+-----------------------+
Table unit_unit_outcome_lookup:
+----------------------------+-----------------+---------+
|unit_unit_outcome_lookup_pk | unit_outcome_fk | unit_fk |
+----------------------------+-----------------+---------+

因此，如果unit_fk = 1和discipline_fk = 5在相应的查找表中，则给定fiddle中的测试数据(下面的链接(，结果应该如下所示：

+---------------+-----------------------+
|unit_outcome 1 | discipline_outcome 10 |
|               |                       |
+---------------+-----------------------+
|unit_outcome 2 | discipline_outcome 7  |
|               |                       |
+---------------+-----------------------+
|unit_outcome 3 | discipline_outcome 1  |
|                 discipline_outcome 8  |
|               | discipline_outcome 9  |
+---------------+-----------------------+

到目前为止，我得到的是以下查询，它只得到unit_outcome -> discipline_outcomes的结果，而不过滤unit和unit_unit_outcome_lookup表，例如unit_fk = '1'以及规程fk:

SELECT unit_outcome, 
GROUP_CONCAT(discipline_outcome SEPARATOR '|') AS discipline_outcomes
FROM unit_outcome u
LEFT JOIN unit_outcome_discipline_outcome_lookup l ON u.unit_outcome_pk = l.unit_outcome_fk
LEFT JOIN discipline_outcome d ON l.discipline_outcome_fk = d.discipline_outcome_pk
WHERE d.discipline_fk = '5' 
GROUP BY unit_outcome

我需要将这两个表合并到上面的查询中，以便只有对应于给定的unit_fk和instraine_fk的unit_outcomes。但不知道该怎么做。

db fiddle

Answer 1

您必须使用INNER联接来联接所有表，如下所示：

SELECT u.unit_outcome, 
GROUP_CONCAT(d.discipline_outcome ORDER BY d.discipline_outcome_pk SEPARATOR '|') AS discipline_outcomes
FROM unit_outcome u
INNER JOIN unit_outcome_discipline_outcome_lookup l ON u.unit_outcome_pk = l.unit_outcome_fk
INNER JOIN discipline_outcome d ON l.discipline_outcome_fk = d.discipline_outcome_pk
INNER JOIN unit_unit_outcome_lookup uu ON uu.unit_outcome_fk = u.unit_outcome_pk
WHERE d.discipline_fk = '5' AND uu.unit_fk = '1'
GROUP BY u.unit_outcome