title: Concatenating rows in relation to a JOIN
body: <p>Suppose I have a cooking show:</p>
  
  <pre><code>cookingepisodes
  id | date
  ---------------
  1  | A
  2  | B
  3  | C
  4  | D
  …
  </code></pre>
  
  <p>This show reviews products in these categories (left) and are linked by the table to the right:</p>
  
  <pre><code>tests               testitems
  id | name           id | episodeid | testid | name
  ------------        ------------------------------------
  1  | cutlery        1  | 1         | 1      | Forks
  2  | spices         2  | 2         | 1      | Knives
                      3  | 4         | 1      | Spoons
                      4  | 4         | 2      | Oregano
  </code></pre>
  
  <p><strong>My desired output</strong> is this:</p>
  
  <pre><code>showid | testid | testname
       4 | 1,2    | cutlery, spices
       3 | NULL   | NULL
       2 | 1      | cutlery
       1 | 1      | cutlery
  </code></pre>
  
  <p>I've tried using this query, and it works as long as I don't need to concatenate the results (when there are two tests on the same episode). Then the join will create multiple rows based on the number of </p>
  
  <pre><code>SELECT DISTINCT e.*, i.testid, t.name AS testname
  FROM cookingepisodes AS e
  LEFT OUTER JOIN testitems AS i ON i.episodeid = e.id
  LEFT OUTER JOIN tests AS t ON i.testid = t.id
  ORDER BY e.date DESC
  </code></pre>
  
  <p>I've also tried something like this, but I can't get it to work because of the outer block reference (e.id):</p>
  
  <pre><code>JOIN (
    SELECT GROUP_CONCAT(DISTINCT testid) 
    FROM testitems 
    WHERE testitems.episodeid = e.id
  ) AS i
  </code></pre>
  
  <p>Any tips on how I can solve this without restructuring the database?</p>