getting userbase vote average and individual user's vote in the same query?

Question

Here goes:

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

In one query (if possible) I'd like to return:

T1.id, T1.desc, AVG(T2.vote), T2.vote (for user viewing the page)

I can get the first 3 items with:

SELECT T1.id, T1.desc, AVG(T2.vote)
FROM T1
LEFT JOIN T2 ON T1.id=T2.id
GROUP BY T1.id

and I can get the first, second, and fourth items with:

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

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:

SELECT T1.id
     , T1.desc
     , AVG(T2.vote)
     , (SELECT T2.vote 
          FROM T2 
         WHERE T2.userid='1') AS userVote 
   etc 
   etc

but I get an error that the select returns more than one row...

Help?

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.

Answer 1

I think this is it:

SELECT T1.id, T1.desc, T3.avgVote, T2.vote
FROM T1
  LEFT OUTER JOIN
    (SELECT id, AVG(vote) AS avgVote FROM T2)
    AS T3 ON T1.id=T3.id
  LEFT OUTER JOIN
    T2 ON T1.id=T2.id AND T2.userid=<UserID>

Answer 2

Make your subquery correlated with the overall query. I think this is what you wanted:

SELECT t1.id, t1.descr, AVG(t2.vote),
   (SELECT t2.vote FROM t2 WHERE t2.userid = 1 AND t1.id = t2.id) AS uservote
FROM t1
LEFT JOIN t2 USING (id)
GROUP BY t1.id

The result I get is:

+------+---------+--------------+----------+
| id   | descr   | AVG(t2.vote) | uservote |
+------+---------+--------------+----------+
|    1 | lovely  |       3.3333 |        3 |
|    2 | ugly    |       3.0000 |        1 |
|    3 | slender |         NULL |     NULL |
+------+---------+--------------+----------+
3 rows in set (0.00 sec)