MySQL Table Joining With AVG()

Question

I have a "ratings" table, that contains (as a foreign key) the ID for the thing that it is rating. There are possibly multiple ratings for a thing, or no ratings for a value.

I want to join tables to see the different ratings for all the different IDs, but right now I'm having trouble viewing things that have no ratings. For example:

mysql> select avg(ratings.rating), thing.id from ratings, things where ratings.thingId = thing.id group by thing.id;
+----------------------+----+
|  avg(ratings.rating) | id |
+----------------------+----+
|               6.3333 |  1 |
|               6.0000 |  2 |
+----------------------+----+

Is there any way to modify my select query to also include IDs that have no ratings? I tried modifying the statement to say where ratings.thingId = thing.id or thing.id > 0 but that doesn't seem to help.

Thanks and sorry if it's unclear.

Answer 1

SELECT  AVG(ratings.rating),
        thing.id
    FROM things
        LEFT OUTER JOIN ratings
            ON ratings.thingId = things.id
    GROUP BY thing.id

Answer 2

You're currently performing an INNER JOIN, which eliminates things records with no associated ratings. Instead, an OUTER JOIN...

SELECT AVG(COALESCE(ratings.rating, 0)), thing.id 
FROM things
LEFT JOIN ratings ON things.id = ratings.thingId
GROUP BY thing.id

Will return ALL things, regardless of whether or not they have ratings. Note the use of COALESCE(), which will return the first non-NULL argument - thus things with no ratings will return 0 as their average.