Help with MySQL Join Statement

Question

Hi,

I just built a website and have realised that I need to have a top 3 highest rated albums.. I haven't built in something that keeps track of the ratings. Ratings are stored separately. Can someone show me how to put these together please.

SELECT id, name FROM albums LIMIT 3

SELECT rating FROM ratings WHERE url=CONCAT('albums/show/', album.id)

Let me just flesh it out a bit. I need to get back the following:

From the albums table. id, name. From the ratings table I need to get back the average rating. ROUND((rating+rating+rating) / total ratings)

The ratings. Users can rate everything on my website so I have a generic ratings table. The rating is stored with the url of the page it applies to. Hence, to get album ratings I need to have 'albums/show/{album_id}'. In hind sight I should have had a type and id field but it is a bit late now with a lunch iminient.

Any help is much appreciated.

Answer 1

SELECT
  a.id,
  a.name,
  AVG(r.rating) AS average
FROM
    albums a
  LEFT JOIN
    ratings r
  ON
    r.url = CONCAT('albums/show/', a.id)
GROUP BY
  a.id
ORDER BY
  average DESC
LIMIT 3

(Untested, see AVG())