Nested SELECT statement with LEFT JOIN

Question

I can’t understand for life what happened to this SQL statement and why it does not produce any results. If I take the LEFT JOIN, it works, and what is wrong with it?

SELECT b.id, r.avg_rating FROM items AS b LEFT JOIN ( SELECT avg(rating) as avg_rating FROM ratings GROUP BY item_id ) AS r ON b.id = r.item_id WHERE b.creator = " . $user_id . " AND b.active = 1 AND b.deleted = 0 ORDER BY b.order ASC, b.added DESC 

Thank you for your help.

Answer 1

add the item_id column to your subquery (I guarantee that it will work), so the ON clause can find r.item_id

 SELECT item_id, avg(rating) as avg_rating FROM ratings GROUP BY item_id