title: Many To Many Table Join With Pivot
body: <p>I currently have two tables similar to <code>users</code> and <code>programs</code> that are linked through a many-to-many relationships by way of a <code>link</code> table.</p>
  
  <pre><code>mysql&gt; select * from users;
  +----+----------+
  | id | name     |
  +----+----------+
  |  1 | Jonathan |
  |  2 | Little   |
  |  3 | Annie    |
  |  4 | Bob      |
  +----+----------+
  4 rows in set (0.00 sec)
  
  mysql&gt; select * from programs;
  +----+----------------------+
  | id | name                 |
  +----+----------------------+
  |  1 | Microsoft Word       |
  |  2 | Microsoft Excel      |
  |  3 | Microsoft PowerPoint |
  +----+----------------------+
  3 rows in set (0.00 sec)
  
  mysql&gt; select * from link;
  +---------+------------+
  | user_id | program_id |
  +---------+------------+
  |       1 |          1 |
  |       1 |          2 |
  |       1 |          3 |
  |       2 |          2 |
  |       3 |          1 |
  |       3 |          4 |
  +---------+------------+
  6 rows in set (0.00 sec)
  </code></pre>
  
  <p>I understand how to join the tables and return a result of this sort:</p>
  
  <pre><code>mysql&gt; select users.name, programs.name from linker
      -&gt; join users on users.id = linker.user_id
      -&gt; join programs on programs.id = linker.program_id;
  +----------+----------------------+
  | name     | name                 |
  +----------+----------------------+
  | Jonathan | Microsoft Word       |
  | Jonathan | Microsoft Excel      |
  | Jonathan | Microsoft PowerPoint |
  | Little   | Microsoft Excel      |
  | Annie    | Microsoft Word       |
  +----------+----------------------+
  </code></pre>
  
  <p>But what I am really looking for is a little more complicated:</p>
  
  <pre><code>+----------+-----------------------------------------------------+
  | name     | name                                                |
  +----------+-----------------------------------------------------+
  | Jonathan | Microsoft Word,Microsoft Excel,Microsoft PowerPoint |
  | Little   | Microsoft Excel                                     |
  | Annie    | Microsoft Word                                      |
  +----------+-----------------------------------------------------+
  </code></pre>
  
  <p>I assume there is a <code>GROUP_CONCAT()</code> thrown into the command somewhere, but I cannot seem to keep the results from looking like this:</p>
  
  <pre><code>mysql&gt; select users.name, group_concat(programs.name) from linker
      -&gt; join users on users.id = linker.user_id
      -&gt; join programs on programs.id = linker.program_id;
  +----------+------------------------------------------------------------------------------------+
  | name     | group_concat(programs.name)                                                        |
  +----------+------------------------------------------------------------------------------------+
  | Jonathan | Microsoft Word,Microsoft Excel,Microsoft PowerPoint,Microsoft Excel,Microsoft Word |
  +----------+------------------------------------------------------------------------------------+
  </code></pre>
  
  <p>Can anybody point me in the right direction?</p>