Join MySQL tables with AVG ()

Question

I have a “ratings” table that contains (as a foreign key) identifier for what it is rated. Perhaps there are several ratings for a thing or not ratings for value.

I want to join the tables to see different ratings for all different identifiers, but now I have problems viewing things that don't have ratings. For instance:

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 a way to modify my selection request to also include identifiers that do not have ratings? I tried to change the statement to say where ratings.thingId = thing.id or thing.id > 0, but that doesn't seem to help.

Thank you and sorry if this is 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

INNER JOIN, things ratings. OUTER JOIN...

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

ALL things, , ratings. COALESCE(), , NULL, things ratings 0 .

Answer 3

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