title: MySQL - How to modify complex inner join and concat query?
body: <p>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 <code>learning_event_presentation_lookup</code> where <code>presentation_pk = $presentation</code> The lookup table contains:</p>
  
  <p><code>learning_event_fk</code> and <code>presentation_fk</code></p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have tried adding the following after the <code>INNER JOIN</code>s but get an error "Unknown column 'learning_event_presentation_lookup.learning_event_fk' in 'where clause'" because the table <code>learning_event_presentation_lookup</code> is not in the select queries.  But I am unsure of how to add that table in the existing query...</p>
  
  <pre><code>WHERE learning_event_presentation_lookup.learning_event_fk = le.learning_event_pk AND learning_event_presentation_lookup.presentation_fk = presentation.presentation_pk
  </code></pre>
  
  <p><a href="https://www.db-fiddle.com/f/euDEW1BgYu9KbVDbprkYPP/0" rel="nofollow noreferrer">DB fiddle</a></p>