Nested SELECT statement with LEFT JOIN

9
votes

I can't for the life of me figure out what's wrong with this SQL statement and why it's not producing any results. If I take out the LEFT JOIN is works, so what's 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

Would appreciate the help greatly.

1
phpmysqlsqlleft-join

1 Answers

21
votes

add the item_id column in 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