MySQL Ratings From Two Tables
- by DirtyBirdNJ
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 place now is an EPIC band-aid and will only cause me problems long term... please help!
