MySQL Table Joining With AVG()

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.

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

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.

SELECT p.id,p.title,(select round(avg(pr.rating),1) from post_rating pr where pr.postid=p.id)as AVG FROM posts p