从数据库返回平均评级（SQL）

Question

I'm trying to create a rating system, where the user can rate pictures/videos/audio etc. I have two tables at the moment

Table: products
Cols:  product_id[PK] | name | category | type | link

This is the products table and contains information about the products. If you're confused by products, think of the "product" as the image/video/audio, I named it like this simply for allowing me to understand it easier. The second table is the ratings

Table: product_ratings
Cols:  rating_id[PK] | rating | product_id | timestamp

This table stores information about the rating the user has given.

I want a page where it will display the highest rating (on average) for all "products". So far, I've looked through SA, and found the follow piece of code:

SELECT
p.product_id, p.name,
AVG(pr.rating) AS rating_average
FROM products p
INNER JOIN product_ratings pr
ON pr.product_id = p.product_id
WHERE p.product_id = 1

This just returns the average rating for a specific product_id, How would I go about getting ALL the product_ids and their average rating, and how would I find the highest one via PHP?

I've tried:

WHERE p.product_id < 1 AND p.product_id < 30

But this just returns product_id of 2, with it's name and average_rating, which I don't understand.

Guidance/links to material are welcome

Answer 1

SELECT
    p.product_id,
    p.name,
    AVG(pr.rating) AS rating_average
FROM products p
INNER JOIN product_ratings pr
ON pr.product_id = p.product_id
GROUP BY p.product_id
ORDER BY rating_average DESC
LIMIT 1