getting userbase vote average and individual user's vote in the same query?
- by Andrew Heath
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.
