title: SQL question: how to find rows that share all of the same rows in a composite table?
body: <p>I'm working on my SQL project using the Oracle database for class, and I'm asked a question that I see far too often.</p>
  <p>You have three tables:</p>
  <ul>
  <li>STUDENT: SNO, SNAME</li>
  <li>CLASS: CNO, CNAME</li>
  <li>ATTENDANCE: SNO, CNO, Grade</li>
  </ul>
  <p>The question I keep finding is of a similar type: Find the names of the students that attend in all of the classes that &quot;John&quot; (or anyone else) attends.</p>
  <p>John attends three classes, so I have to find the students that also attend those three classes (could be more, but those three must be there). However, I won't always know how many classes John (or whoever) attends, so it can't be hardcoded like that.</p>
  <pre><code>SELECT jclass.CNO  
  FROM attendance jclass  
  INNER JOIN student on jclass.SNO = student.SNO  
  WHERE student.SNAME = 'John';
  </code></pre>
  <p>This gets me the classes that John attends. I tried to add the identifier for the other students:</p>
  <pre><code>SELECT student.SNAME  
  FROM student  
  INNER JOIN attendance on student.SNO = attendance.SNO  
  INNER JOIN class on attendance.CNO = class.CNO  
  WHERE student.SNAME &lt;&gt; 'John' 
    AND class.CNO IN (SELECT jclass.CNO  
                      FROM attendance jclass  
                      INNER JOIN student on jclass.SNO = student.SNO  
                      WHERE student.SNAME = 'John');
  </code></pre>
  <p>However, this only gets me the students that appear in <strong>at least</strong> one of John's classes, rather than all of them. I can see <em>why</em> it's doing this, but I'm not sure how to fix it. It's the one big struggle I'm having with SQL.</p>