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.
This is how my data looks like;
Table 1: 'receivedfeedback'
'user' 'date'
henk 2008-04-06
bert 2009-04-09
bert 2009-04-12
albert 2009-04-28
albert 2009-08-27
Table 2: 'ideaproposals'
'user' 'date'
henk 2008-03-04
bert 2009-04-06
albert 2009-04-26
albert 2009-08-24
albert 2009-09-18
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;
Table 1: 'receivedfeedback'
'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
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:
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 < b.date
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.
Any suggestions are greatly appreciated.