MySQL - 如何修改复杂的内连接和字符串拼接查询？

Question

我有以下查询，它可以正常工作，但需要创建一个版本，仅返回在查找表“learning_event_presentation_lookup”上存在匹配项的学习事件，其中“presentation_pk = $presentation”。查找表包含：
学习事件外键（learning_event_fk）和演示文稿外键（presentation_fk）。

SELECT CONCAT('program:', program_pk) AS global_id,
       program_name AS name,
       NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('year:', year_pk) AS global_id,
       year_name AS name,
       CONCAT('program:', program_fk) AS parent_global_id
FROM year 
UNION ALL
SELECT 
       CONCAT('year:', year_fk, ',unit:', unit_name) AS global_id,
       unit_name AS name,
       CONCAT('year:', year_fk) AS parent_global_id
FROM unit
UNION ALL
SELECT 
       CONCAT('year:', year_fk, ',unit:', unit_name, ',rotation_discipline_block:', rotation_discipline_block_name) AS global_id,
       rotation_discipline_block_name AS name,
       CONCAT('year:', year_fk, ',unit:', unit_name) AS parent_global_id
FROM rotation_discipline_block rdb
INNER JOIN unit u ON u.unit_pk = rdb.unit_fk
UNION ALL
SELECT 
       CONCAT('year:', year_fk, ',unit:', unit_name, ',rotation_discipline_block:', rotation_discipline_block_name, ',learning_event:', learning_event_name) AS global_id,
       learning_event_name AS name,
       CONCAT('year:', year_fk, ',unit:', unit_name, ',rotation_discipline_block:', rotation_discipline_block_name) AS parent_global_id
FROM learning_event le
INNER JOIN rotation_discipline_block rdb ON rdb.rotation_discipline_block_pk = le.rotation_discipline_block_fk
INNER JOIN unit u ON u.unit_pk = rdb.unit_fk
INNER JOIN year y ON u.year_fk = y.year_pk
ORDER BY name

我尝试在INNER JOIN后面添加以下内容，但是出现了错误“where子句中未知的列'learning_event_presentation_lookup.learning_event_fk'”，因为表'learning_event_presentation_lookup'没有在选择查询中。但我不确定如何将该表添加到现有查询中...

WHERE learning_event_presentation_lookup.learning_event_fk = le.learning_event_pk AND learning_event_presentation_lookup.presentation_fk = presentation.presentation_pk

DB fiddle

- IlludiumPu36

Answer 1

由于这是一组联合，查询的其余部分可以忽略。我们只关心这个:

SELECT 
       CONCAT('year:', year_fk, ',unit:', unit_name, ',rotation_discipline_block:', rotation_discipline_block_name, ',learning_event:', learning_event_name) AS global_id,
       learning_event_name AS name,
       CONCAT('year:', year_fk, ',unit:', unit_name, ',rotation_discipline_block:', rotation_discipline_block_name) AS parent_global_id
FROM learning_event le
INNER JOIN rotation_discipline_block rdb ON rdb.rotation_discipline_block_pk = le.rotation_discipline_block_fk
INNER JOIN unit u ON u.unit_pk = rdb.unit_fk
INNER JOIN year y ON u.year_fk = y.year_pk
ORDER BY name

我们通过在 learning_event_pk 上与之连接，添加了 learning_event_presentation_lookup。

INNER JOIN learning_event_presentation_lookup lepl ON lepl.learning_event_fk = le.learning_event_pk

现在我们可以将其限制为仅学习与特定演示文稿相关的事件。

WHERE lepl.presentation_fk = :presentation_fk

现在一起来，只搜索与演示文稿 #23 相关的学习事件。

SELECT 
       CONCAT('year:', year_fk, ',unit:', unit_name, ',rotation_discipline_block:', rotation_discipline_block_name, ',learning_event:', learning_event_name) AS global_id,
       learning_event_name AS name,
       CONCAT('year:', year_fk, ',unit:', unit_name, ',rotation_discipline_block:', rotation_discipline_block_name) AS parent_global_id
FROM learning_event le
INNER JOIN rotation_discipline_block rdb ON rdb.rotation_discipline_block_pk = le.rotation_discipline_block_fk
INNER JOIN unit u ON u.unit_pk = rdb.unit_fk
INNER JOIN year y ON u.year_fk = y.year_pk
INNER JOIN learning_event_presentation_lookup lepl ON lepl.learning_event_fk = le.learning_event_pk
WHERE lepl.presentation_fk = 23
ORDER BY name

DB Fiddle

- Schwern