OK, I think there must be a way to do this, but I'm not having much luck. I think there is a JOIN statement to do what I'm looking for, but I can't find the right combination.

Here's what I have:
* One table contains a list of items
* One table contains a list of item reviews

Basically, I need to do a query where I get a list of all the items and their related average review ratings.

I've got the first bit, I do:
SELECT FROM table1, table2 WHERE table1.id=table2.item GROUP BY ITEM
which will give me one row for each item and I can get it's average review rating.

So everything's good!... Not quite.

The problem arises as not all items have a review. This means that they don't show up in this query (obviously, as they don't have a match where table1.id=table2.item).

So now the question. How can I do this query so that I get the items with no reviews output too (i.e. with a NULL or 0 review rating), or am I going to just do two queries?

I'm not too good at the mySQL join stuff, but I believe your looking for the LEFT JOIN. I can't really say how to use it so I guess your last option is to google it.

Yes, the left join sounds about right. You should be able to do some variation on this:

SELECT table1.itemid, table1.itemname, SUM(table2.rating) / COUNT(table2.rating) AS averagerating
FROM table1 LEFT OUTER JOIN table2 ON table1.itemid = table2.itemid
GROUP BY table1.itemid, table1.itemname


This will take advantage of the fact that SQL Server won't complain about a possible divide by zero occurring and just return null - not sure about other SQL engines.

Thanks! The LEFT OUTER JOIN did it!