MySQL-如何修改复杂的内部联接和concat查询? [英] MySQL - How to modify complex inner join and concat query?
问题描述
我有以下查询,该查询很好用,但需要创建一个查询版本,该查询版本仅返回在查找表上存在匹配项的学习事件learning_event_presentation_lookup
,其中presentation_pk = $presentation
查找表包含:
I have the following query which works fine, but need to create a version of it which returns only learning events where there is a match on a lookup table learning_event_presentation_lookup
where presentation_pk = $presentation
The lookup table contains:
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
不在选择查询中.但是我不确定如何在现有查询中添加该表...
I have tried adding the following after the INNER JOIN
s but get an error "Unknown column 'learning_event_presentation_lookup.learning_event_fk' in 'where clause'" because the table learning_event_presentation_lookup
is not in the select queries. But I am unsure of how to add that table in the existing query...
WHERE learning_event_presentation_lookup.learning_event_fk = le.learning_event_pk AND learning_event_presentation_lookup.presentation_fk = presentation.presentation_pk
推荐答案
由于这是一堆联合,因此其余查询可以忽略.我们只关心这个:
Since this is a bunch of unions, the rest of the query can be ignored. We only care about this:
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
来添加它.
We add learning_event_presentation_lookup
by joining with it on the learning_event_pk
.
INNER JOIN learning_event_presentation_lookup lepl ON lepl.learning_event_fk = le.learning_event_pk
现在我们可以将其限制为仅学习与特定演示文稿相关的事件.
And now we can restrict it to only learning events with associated with specific presentations.
WHERE lepl.presentation_fk = :presentation_fk
现在,一起搜索仅与演示文稿#23相关的学习事件.
Altogether now, searching for only learning events associated with presentation #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
这篇关于MySQL-如何修改复杂的内部联接和concat查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!