MySQL Ratings From Two Tables
I am using MySQL and PHP to build a data layer for a flash game. Retrieving lists of levels is pretty easy, but I've hit a roadblock in trying to fetch the level's average rating along with it's pointer information. Here is an example data set:
levels Table:
level_id | level_name
1 | Some Level
2 | Second Level
3 | Third Level
ratings Table:
rating_id | level_id | rating_value
1 | 1 | 3
2 | 1 | 4
3 | 1 | 1
4 | 2 | 3
5 | 2 | 4
6 | 2 | 1
7 | 3 | 3
8 | 3 | 4
9 | 3 | 1
I know this requires a join, but I cannot figure out how to get the average rating value based on the level_id when I request a list of levels. This is what I'm trying to do:
SELECT levels.level_id, AVG(ratings.level_rating WHERE levels.level_id = ratings.level_id) FROM levels
I know my SQL is flawed there, but I can't figure out how to get this concept across. The only thing I can get to work is returning a single average from the entire ratings table, which is not very useful.
Ideal Output from the above conceptually valid but syntactically awry query would be:
level_id | level_rating
1| 3.34
2| 1.00
3| 4.54
My main issue is I can't figure out how to use the level_id of each response row before the query has been returned. It's like I want to use a placeholder... or an alias... I really don't know and it's very frustrating. The solution I have in 开发者_C百科place now is an EPIC band-aid and will only cause me problems long term... please help!
If your levels
table is InnoDB
:
SELECT l.level_id, COALESCE(AVG(level_rating), 0)
FROM levels l
LEFT JOIN
ratings r
GROUP BY
l.id
If your levels
table is MyISAM
:
SELECT l.level_id,
(
SELECT COALESCE(AVG(level_rating), 0)
FROM rating r
WHERE r.level_id = l.level_id
)
FROM levels l
Subquery is more efficient for MyISAM
, since it's heap-based and GROUP BY
would require sorting and/or materialization.
Give this a try:
SELECT level_id, AVG(ratings.level_rating)
FROM levels JOIN ratings USING(level_id)
GROUP BY level_id
Note that level_id
is never referenced to a specific table, as it's used in the USING
part of the join
You need to add a GROUP BY
clause. Try this:
SELECT levels.level_id
, AVG(ratings.level_rating)
FROM levels
JOIN ratings ON levels.level_id = ratings.level_id
GROUP BY levels.level_id
When you use an aggregate function (AVG, SUM, COUNT, MIN, MAX), you need to group your query by the other attributes you are selecting. This is why SELECT AVG(ratings.level_rating)
works without a GROUP BY
, but trying to select the level_id as well requires the group by.
精彩评论