title: mySQL Query design - calculating a vote score for multiple content items
body: <p>I have a content items table structured like</p>
  
  <pre><code>|  contentid |   message     |    categoryid  |  userid  |    dateadded | etc.. 
       15          foo bar            3             4           somedate
       16          more foo bar       3             4           somedate
       16          foo stuff          3             4           somedate
  </code></pre>
  
  <p>and a votes table, where direction = 1 = an up vote, and = 2 being a down vote.</p>
  
  <pre><code>|  voteid   |  contentid |  userid  |  direction |    dateadded
      7             15          4            1           some date
      8             15          6            1           some date
      9             15          17           2           some date
  </code></pre>
  
  <p>And I'd like to select a set of content items, having an additional column on the end with its calculated score based on the votes in the votes table.</p>
  
  <p>Previously, I had a 'score' column attached to the content table, and each time a vote was cast, it would update its score. This was done so I wouldnt have to have a more complex query to calculate scores on each SELECT, but I'd like to change this now.</p>
  
  <p>This votes table was designed a while ago, so if changing all the votes values to something other than 1 or 2 (perhaps -1 for a downvote) would make it easier, I will update the entire table.</p>
  
  <p>What would the query be to pull all content items, each with a score in a calculated column?</p>