title: How to do a JOIN including a count based on is equal and greater than?
body: <p>I have two tables to study the effect of peer feedback on future contribution. Therefore I need to Join the data from the 'ideaproposals' table to the 'receivedfeedback' table. </p>
  
  <p>This is how my data looks like;</p>
  
  <p>Table 1: 'receivedfeedback'</p>
  
  <pre><code>     'user'    'date'    
        henk      2008-04-06
        bert      2009-04-09
        bert      2009-04-12
        albert    2009-04-28
        albert    2009-08-27
  </code></pre>
  
  <p>Table 2: 'ideaproposals'</p>
  
  <pre><code>     'user'    'date'
        henk      2008-03-04
        bert      2009-04-06
        albert    2009-04-26
        albert    2009-08-24
        albert    2009-09-18
  </code></pre>
  
  <p>I would like to add an extra column to Table 1: 'receivedfeedback' that counts the number of future idea proposals from Table2: 'ideaproposals' and thus takes the 'date' of each row from Table 1: 'receivedfeedback' and also a match of 'user' into account in such a way that it only counts the number of rows that are later than the date from Table 1: 'receivedfeedback'. The result should be;</p>
  
  <p>Table 1: 'receivedfeedback'</p>
  
  <pre><code>     'user'    'date'         'numberoffutureideas'
        henk      2008-04-06     0
        bert      2009-04-09     0
        bert      2009-04-12     0
        albert    2009-04-28     2
        albert    2009-08-27     1
  </code></pre>
  
  <p>The code that I came up with looks like the following but I think the conditions should not be on the JOIN ON level but on the calculation level. By running this query the result is either the total amount of idea proposals or NULL:</p>
  
  <pre><code>SELECT * FROM receivedfeedback AS a 
  LEFT JOIN ( 
  SELECT count(ideaproposals.date) as numberoffutureideas, ideaproposals.user, ideaproposals.date 
  FROM ideaproposals 
  GROUP BY ideaproposals.user 
  ) AS b 
  ON a.user = b.user AND a.date &lt; b.date
  </code></pre>
  
  <p>Update is not included yet since I wanted to test the results of the JOIN first. But this is welcome in the suggestion of course. </p>
  
  <p>Any suggestions are greatly appreciated.  </p>