0

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.

Togink
  • 1
  • 1
  • "Update is not included yet". And long may it be thus. – Strawberry Apr 29 '15 at 23:37
  • @strawberry, not sure how to interpret you comment. But are you suggesting that the UPDATE function is essential here to make it all work? – Togink Apr 30 '15 at 08:58
  • I'm suggesting the opposite. In general, it's bad practice to store data that can be easily derived from other data 'on-the-fly'. – Strawberry Apr 30 '15 at 10:48
  • A simple way to get the result in a select: http://nopaste.nl/2ohsXMbIjw - I don't get it to update it in one query, since MySQL doesn't allow to update the table you're selecting, without using complex subqueries e.g. http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause http://stackoverflow.com/questions/10402678/mysql-update-from-select-same-table – Arie Apr 30 '15 at 12:10
  • @Arie, thanks for your comment. This gives the right results. I can export them and create a new database instead of doing an update. – Togink Apr 30 '15 at 18:00

0 Answers0