title: Join two tables (with a 1-M relationship) where the second table needs to be 'flattened' into one row
body: <p>Given the following tables:</p>
  
  <p>Student</p>
  
  <pre><code>+----+-------+
  | id | Name  |
  +----+-------+
  | 1  | Chris |
  | 2  | Joe   |
  | 3  | Jack  |
  +----+-------+
  </code></pre>
  
  <p>Enrollment</p>
  
  <pre><code>+---------------+------------+-----------+----------+
  | enrollment_id | student_id | course_id | complete |
  +---------------+------------+-----------+----------+
  | 1             | 1          | 55        | true     |
  | 2             | 1          | 66        | true     |
  | 3             | 1          | 77        | true     |
  | 4             | 2          | 55        | true     |
  | 5             | 2          | 66        | false    |
  | 6             | 3          | 55        | false    |
  | 7             | 3          | 66        | true     |
  +---------------+------------+-----------+----------+
  </code></pre>
  
  <p>I would like the following</p>
  
  <pre><code>+----+-------+-----------+-----------+-----------+
  | id | Name  | Course 55 | Course 66 | Course 77 |
  +----+-------+-----------+-----------+-----------+
  | 1  | Chris | true      | true      | true      |
  | 2  | Joe   | true      | false     | NULL      |
  | 3  | Jack  | false     | true      | NULL      |
  +----+-------+-----------+-----------+-----------+
  </code></pre>
  
  <p><strong>Note 1:</strong> I know mysql can't have dynamic columns (correct me if I'm wrong!) so I am happy with the query starting as:</p>
  
  <pre><code>SELECT id, name, course_55, course_66, course_77 etc...
  </code></pre>
  
  <p>I happy with this because there is a fixed number of courses (4 to be exact). <strong>Ideally</strong> I would want it to be dynamic; that is, not having to manually write each course in the SELECT clause. </p>
  
  <p><strong>Note 2:</strong> This needs to mysql pure - I don't want to resort to PHP.</p>
  
  <p>The database currently stands at 10000+ students with 10000+ * 4 enrollments (as there is exactly 4 courses, and every student is in all 4 modules). </p>
  
  <p><strong>Note 3:</strong> Student.user_id is indexed and so is enrollment.enrollment_id, enrollment.student_id, and enrollment.course_id. </p>