MySQL Ratings From Two Tables

Question

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!

Answer 1

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.

Answer 2

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

Answer 3

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.