title: getting userbase vote average and individual user's vote in the same query?
body: <p>Here goes:</p>
  
  <pre><code>T1
  [id] [desc]
   1    lovely
   2    ugly
   3    slender
  
  T2
  [id] [userid] [vote]
   1      1       3
   1      2       5
   1      3       2
   2      1       1
   2      2       4
   2      3       4
  </code></pre>
  
  <p>In one query (if possible) I'd like to return:</p>
  
  <pre><code>T1.id, T1.desc, AVG(T2.vote), T2.vote (for user viewing the page)
  </code></pre>
  
  <p>I can get the first 3 items with:</p>
  
  <pre><code>SELECT T1.id, T1.desc, AVG(T2.vote)
  FROM T1
  LEFT JOIN T2 ON T1.id=T2.id
  GROUP BY T1.id
  </code></pre>
  
  <p>and I can get the first, second, and fourth items with:</p>
  
  <pre><code>SELECT T1.id, T1.desc, T2.vote
  FROM T1
  LEFT JOIN T2 ON T1.id=T2.id
  WHERE T2.userid='1'
  GROUP BY T1.id
  </code></pre>
  
  <p>but I'm at a loss as to how to get all four items in one query. I tried inserting a select as the fourth term:</p>
  
  <pre><code>SELECT T1.id
       , T1.desc
       , AVG(T2.vote)
       , (SELECT T2.vote 
            FROM T2 
           WHERE T2.userid='1') AS userVote 
     etc 
     etc
  </code></pre>
  
  <p>but I get an error that the <code>select returns more than one row...</code></p>
  
  <p>Help?</p>
  
  <p>My reason for wanting to do this in one query instead of two is that I want to be able to sort the data within MySQL rather than one it's been split into a number of arrays.</p>