title: Build complex MySQL Query with several Joins
body: <p>I have the following tables:</p>
  
  <p><strong>disciplines</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>id             int(11)  AUTO_INCREMENT
  name           var_char(50)
  speciality     int(11)  -- The ID of the speciality
  </code></pre>
  
  <p><strong>grades</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>id             int(11)  AUTO_INCREMENT
  student_id     int(11)  -- the ID of the student from `students` table
  discipline_id  int(11)  -- the ID of the discipline from `disciplines` table
  grade          int(2)
  </code></pre>
  
  <p>Let's say I have two variables: one holding the ID of the student and the other one holding the ID of the speciality.<br>
  What I need to do is to create a query that returns as a result a table with two columns: <code>discipline</code> and <code>grade</code>. The <code>discipline</code> column should have every discipline that will be studied in the given speciality and the <code>grade</code> column should show the grade for that discipline for that student (from the given variable).<br>
  The rules:<br>
   - The student may have more than one grade for one discipline (as multiple rows in the <strong>grades</strong> table)<br>
   - The student may not have any grades for one discipline. In that case the <code>grades</code> column in the result should show <code>NULL</code> as result   </p>
  
  <p>Example result:    </p>
  
  <pre class="lang-sql prettyprint-override"><code>discipline   |      grade
  -------------|------------
  Math         |         2
  Math         |         2
  Math         |         3
  Programming  |         4
  DB Basics    |      NULL
  and so on... |  
  -------------------------
  </code></pre>
  
  <p>Is it possible to do that with one query?</p>
  
  <p><strong>Edit:</strong>
  I tried to do something like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT disciplines.name, grades.grade
  FROM disciplines
  INNER JOIN grades ON grades.discipline_id = disciplines.id
  WHERE disciplines.speciality = 3 AND grades.student_id = 97
  </code></pre>
  
  <p>The problem here is I do not have the other disciplines from the same speciality, the ones that this student hasn't any grades yet.</p>