title: SQL: Display Full Name + Filter By Subject
body: <p>Here are the following records inside my tables:</p>
  
  <p><strong>EnrollStudents</strong></p>
  
  <pre><code>EnrollID     StudentID  SubjID
  1            1          1
  2            1          2
  3            1          3
  4            2          1
  5            3          2
  </code></pre>
  
  <p><strong>Students</strong></p>
  
  <pre><code>StudentID     UserID     YearID     FirstName     LastName
  1             1          1          John          Doe
  2             3          1          Peter         Pan
  3             7          1          Isaac         Newton
  </code></pre>
  
  <p><strong>Subjects</strong></p>
  
  <pre><code>SubjID     SubjCode     YearID
  1          English      1
  2          Math         1
  3          Science      1
  </code></pre>
  
  <p>and the output should be...</p>
  
  <pre><code>StudentID     FullName
  2             Peter Pan
  3             Isaac Newton
  </code></pre>
  
  <p>I was able to come up with the following SQL statement (thanks to <a href="https://stackoverflow.com/users/44853/lc">Ic.</a>)</p>
  
  <pre><code>SELECT s.StudentID, s.FirstName + ' ' s.LastName AS FullName 
  FROM Students AS s
  LEFT OUTER JOIN EnrollStudents AS es ON s.StudentID = es.StudentID
  GROUP BY s.StudentID, s.FirstName, s.LastName 
  HAVING COUNT(DISTINCT(es.SubjID)) &lt; (SELECT COUNT(*) FROM Subjects)
  </code></pre>
  
  <p>Is there a way that I can filter the query by subject? Let's say I select English, only Isaac Newton can enroll the subject.</p>