title: Stored procedure containing inner join with count not working
body: <p>Let's say i've got a databasetable looking a bit like this, containing information about some assignments.</p>
  
  <pre><code>Id   | ProfessionId      | Title       | Deadline     | DateCreated | ClosingDate
  1    |  5                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012
  2    |  6                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012
  3    |  7                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012
  4    |  7                | Something   | 01-12-2012   | 05-11-2012  | 12-11-2012
  </code></pre>
  
  <p>I want to generate an overview foreach profession (assignments belong to a certain profession) and count the number of assignment in each profession. The overview coming from the database should look like this;</p>
  
  <pre><code>Id | Name           | FriendlyUrl     | Ordinal   | NumberOfAssignments
  5  | Profession 1   | profession-1    | 1         | 1
  6  | Profession 2   | profession-2    | 1         | 1
  7  | Profession 3   | profession-3    | 1         | 2
  8  | Profession 4   | profession-4    | 1         | 0
  </code></pre>
  
  <p>I've currently got a stored procedure returning the overview above, except that the amount of assignments isn't correct. Assignments with a closingdate in the past (we then assume the assignment is closed) shouldn't be taken into the the total number of assignment.</p>
  
  <p>The current stored procedure is like this:</p>
  
  <pre><code>BEGIN
      SELECT  p.Id, 
              p.Naam, 
              p.FriendlyUrl, 
              p.Ordinal, 
              COUNT(a.ProfessionId) AS NumberOfAssignments
      FROM ME_Profession AS p 
      LEFT OUTER JOIN ME_Assignment AS a ON a.ProfessionId = p.Id
      INNER JOIN ME_Client AS c ON a.ClientId = c.Id
      INNER JOIN aspnet_Membership AS m ON m.UserId = c.UserId
      WHERE m.IsApproved = 1 
      GROUP BY p.Id, p.Naam, p.FriendlyUrl, p.Ordinal
  END
  </code></pre>
  
  <p>I've already came up with and modified procedure like the one below, but it doesn't work. It feels like i'm either thinking too difficult or missing something obvious. What could go wrong?</p>
  
  <pre><code>SELECT        p.Id, p.Naam, p.FriendlyUrl, p.Ordinal, pc.NumberOfAssignments
  FROM            ME_Profession AS p 
  INNER JOIN ME_Assignment AS a ON a.ProfessionId = p.Id 
  INNER JOIN ME_Client AS c ON a.ClientId = c.Id
  INNER JOIN aspnet_Membership AS m ON m.UserId = c.UserId
  INNER JOIN (SELECT a2.ProfessionId, COUNT(*) AS NumberOfAssignments FROM ME_Assignment AS a2 GROUP BY a2.ProfessionId WHERE a2.Closingdate &gt; GETDATE()) pc ON p.ProfessionId = pc.ProfessionId
  WHERE        m.IsApproved = 1 AND a.Closingdate &gt; GETDATE()
  GROUP BY p.Id, p.Naam, p.FriendlyUrl, p.Ordinal
  </code></pre>
  
  <p><strong>UPDATE 1:</strong> Added where condition for date</p>