title: MySQL UPDATE with GROUP_CONCAT joining three tables
body: <p>The goal: update T1 with GROUP_CONCAT info from T3 with JOIN across T2.</p>
  
  <p>Here’s a simplified version of the table structures:</p>
  
  <p><strong>T1: xfer_lectures</strong><br />
  Relevant fields: <code>lecture_id</code>, <code>topics</code><br />
  (I'm trying to fill the topics field with a concatenated list of topics designated for that lecture.)</p>
  
  <p><strong>T2: calendar_lecture_topics</strong><br />
  Relevant fields: <code>event_id</code>, <code>topic_id</code><br />
  (T1.lecture_id = T2.event_id)</p>
  
  <p><strong>T3: lecture_topics</strong><br />
  Relevant fields: <code>id</code>, <code>title</code><br />
  (T2.topic_id = T3.event_id)</p>
  
  <p>I can successfully SELECT the info I want with the following query:</p>
  
  <pre><code>SELECT 
      T1.`lecture_id`, GROUP_CONCAT(DISTINCT `title` SEPARATOR '; '), COUNT(*)
  FROM
      `xfer_lectures` T1
      INNER JOIN
          `calendar_lecture_topics` T2
      INNER JOIN
          `lecture_topics` T3 
      ON T1.`lecture_id` = T2.`event_id`
      AND T2.`topic_id` = T3.`id`
      GROUP BY T1.`lecture_id`
  </code></pre>
  
  <p>However, when I try to UPDATE T1 with the concatenated info, I fail. I’ve tried a bunch of versions of the update query, most of which yield errors. This one runs as a valid query but fills every topic field with the same list of ALL the topics in the topics table:</p>
  
  <pre><code> UPDATE 
      `xfer_lectures` T1
   JOIN `calendar_lecture_topics` T2
      ON T1.`lecture_id`=T2.`event_id`
   JOIN `lecture_topics` T3
      ON T2.`topic_id` = T3.`id`
   SET T1.`topics` = (
      SELECT 
      GROUP_CONCAT(`title` SEPARATOR '; ')
      FROM `lecture_topics`
      )
  </code></pre>
  
  <p>I also tried versions where the SELECT statement includes a GROUP_BY clause, but I still ended up with the same list of topics for every record as opposed to the two or three relevant topics per lecture. For example:</p>
  
  <pre><code>SET T1.`topics` = (       
  SELECT        
  GROUP_CONCAT(`title` SEPARATOR '; ')       
  FROM `lecture_topics`
  WHERE T2.`topic_id` = T3.`id` 
  AND T1.`lecture_id`=T2.`event_id`
  GROUP BY T2.`event_id`)
  </code></pre>
  
  <p>Where am I going wrong? I'm not terribly experienced with complex queried so my understanding of both JOINs and grouping is probably flawed. Any assistance will be much appreciated!</p>