Sort results by avg of another table's column

[msnider]

So I have one table called media with columns such as id, date, type, description and another table called ratings with columns such as id, itemId, date, rating. I want to retrieve rows from media sorted by the average of the rating column of the ratings table when grouped by itemId (which is equivalent to media.id) without eliminating results that do not have a row in the ratings table.

 

Here is what I have so far:

 

SELECT media.*, ratings.itemId, AVG(ratings.rating) AS avg_rating FROM media,ratings WHERE media.id=ratings.itemId GROUP BY media.id

 

The problem with it is that it eliminates any rows in media that do not have a row in ratings. Is there anyway to still include these rows with avg_rating set at 0 or -1?

 

OR I tried joining the 2 tables as below but again it eliminates rows with no row in the ratings table:

 

SELECT media.*, AVG(ratings.rating) as avg_rating FROM media INNER JOIN ratings ON media.id=ratings.itemId GROUP BY media.id

 

Any help is much appreciated.

[Barand]

Use LEFT JOIN inplace of INNER JOIN so all media are included even when there is no matching rating record