title: SQL Comparing COUNT values within same table
body: <p>I'm trying to solve a seemingly simple problem, but I think i'm tripping over on my understanding of how the EXISTS keyword works. The problem is simple (this is a dumbed down version of the actual problem) - I have a table of students and a table of hobbies. The students table has their student ID and Name. Return only the students that share the same number of hobbies (i.e. those students who have a unique number of hobbies would not be shown)</p>
  
  <p>So the difficulty I run into is working out how to compare the count of hobbies. What I have tried is this.</p>
  
  <pre><code>SELECT sa.studentnum, COUNT(ha.hobbynum)
  FROM student sa, hobby ha
  WHERE sa.studentnum = ha.studentnum
  AND EXISTS (SELECT *
              FROM student sb, hobby hb
              WHERE sb.studentnum = hb.studentnum 
              AND sa.studentnum != sb.studentnum
              HAVING COUNT(ha.hobbynum) = COUNT(hb.hobbynum)
             )
  GROUP BY sa.studentnum
  ORDER BY sa.studentnum;
  </code></pre>
  
  <p>So what appears to be happening is that the count of hobbynums is identical each test, resulting in all of the original table being returned, instead of just those that match the same number of hobbies.</p>