title: Mysql query to display results of all students for a semester
body: <p>I have a semester_student_marks table with the following structure - </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;table border="1"&gt;
  &lt;tr&gt;
  &lt;th&gt;Semester Id&lt;/th&gt;&lt;th&gt;Student Id&lt;/th&gt;&lt;th&gt;Subject Id&lt;/th&gt;&lt;th&gt;Marks&lt;/th&gt;
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;1234&lt;/td&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;55&lt;/td&gt;&lt;/tr&gt;
  &lt;tr&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;1234&lt;/td&gt;&lt;td&gt;2&lt;/td&gt;&lt;td&gt;65&lt;/td&gt;&lt;/tr&gt;
  &lt;tr&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;3456&lt;/td&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;75&lt;/td&gt;&lt;/tr&gt;
  &lt;tr&gt;&lt;td&gt;1&lt;/td&gt;&lt;td&gt;3456&lt;/td&gt;&lt;td&gt;2&lt;/td&gt;&lt;td&gt;85&lt;/td&gt;&lt;/tr&gt;
  &lt;/table&gt;
  
  &lt;br&gt;&lt;br&gt;
  
  
  &lt;table border="1"&gt;
  &lt;tr&gt;
  &lt;th&gt;Student Id&lt;/th&gt;&lt;th&gt;Subject 1&lt;/th&gt;&lt;th&gt;Subject 2&lt;/th&gt;
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td&gt;1234&lt;/td&gt;&lt;td&gt;55&lt;/td&gt;&lt;td&gt;65&lt;/td&gt;&lt;/tr&gt;
  &lt;tr&gt;&lt;td&gt;3456&lt;/td&gt;&lt;td&gt;75&lt;/td&gt;&lt;td&gt;85&lt;/td&gt;&lt;/tr&gt;
  
  &lt;/table&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>I tried the following query - </p>
  
  <pre><code>select m.student_id,
         if(m.subject_id=8,m.marks,0) as 'S1',
         if(m.subject_id=9,m.marks,0) as 'S2',
         if(m.subject_id=10,m.marks,0) as 'S3' 
  from (select student_id, 
               subject_id, 
               marks 
        from program_exam_marks t 
        where semester_id=1) m;
  </code></pre>
  
  <p>But the result of each student-subject is getting displayed in a separate row as below - </p>
  
  <pre><code>+------------+------+------+------+
  | student_id | S1   | S2   | S3   |
  +------------+------+------+------+
  |       1234 |   55 |    0 |    0 |
  |       1234 |    0 |   67 |    0 |
  |       1234 |    0 |    0 |   74 |
  |     654321 |   55 |    0 |    0 |
  |     654321 |    0 |   87 |    0 |
  |     654321 |    0 |    0 |   60 |
  +------------+------+------+------+
  </code></pre>
  
  <p>How do I get my results as - </p>
  
  <pre><code>+------------+------+------+------+
  | student_id | S1   | S2   | S3   |
  +------------+------+------+------+
  |     1234   |   55 |   67 |   74 |       
  |     654321 |   75 |   87 |   60 |
  +------------+------+------+------+
  </code></pre>
  
  <p>My SQL skills are elementary at best so any/all suggestions/solutions would be greatly appreciated. </p>
  
  <p>Thank you.</p>