mysql复杂查询修改,MySQL-如何修改复杂的内部联接和concat查询？

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 and 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

I have tried adding the following after the INNER JOINs 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

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

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