title: SQL - Selecting counts from multiple tables
body: <p>Here is my problem (I'm using SQL Server)</p>
  
  <p>I have a table of <code>Students (StudentId, Firstname, Lastname, etc)</code>.</p>
  
  <p>I have a table that records <code>StudentAttendance (StudentId, ClassDate, etc.)</code></p>
  
  <p>I record other student activity (I'm generalizing here for simplicity) such as a <code>Papers</code> table <code>(StudentId, PaperId, etc.)</code>. There may be anywhere from zero to 20 papers turned in. Similarly, there is a table called <code>Projects (StudentId, ProjectId, etc.)</code>. Same deal as with <code>Papers</code>.</p>
  
  <p>What I'm trying to do is create a list of counts for students who have attendance over a certain level (say 10 attendances). Something like this:</p>
  
  <pre><code>ID    Name      Att    Paper Proj
  123   Baker     23     0     2
  234   Charlie   26     5     3
  345   Delta     13     3     0
  </code></pre>
  
  <p>Here is what I have:</p>
  
  <pre><code>select 
    s.StudentId,
    s.Lastname,
    COUNT(sa.StudentId) as CountofAttendance,
    COUNT(p.StudentId) as CountofPapers
  from Student s
  inner join StudentAttendance sa on (s.StudentId = sa.StudentId)
  left outer join Paper p on (s.StudentId = p.StudentId)
  group by s.StudentId, s.Lastname
  Having COUNT(sa.StudentId) &gt; 10
  order by CountofAttendance
  </code></pre>
  
  <p>If the <code>CountofPaper</code> and join (either inner or left outer) to the <code>Papers</code> table is commented out, the query works fine. I get a nice count of students who have attended at least 10 classes.  </p>
  
  <p>However, if I put in the <code>CountofPapers</code> and the join, things get crazy. With a left outer join, any students with papers just show their attendance count in the paper column.  With an inner join, both attendance and paper counts seem to multiple off each other.</p>
  
  <p>Guidance needed and appreciated.</p>
  
  <p>Dave</p>