Help with MySQL Join Statement

Question

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.

Do you thing you could add the album Id to the ratings table? That would make your join MUCH more efficient. That CONCAT('albums/show/', a.id) in the join is going to kill performance — lexu
– lexu, Commented May 3, 2010 at 11:18

jensgram · Accepted Answer · 2010-05-03 11:15:07Z

2

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())

edited May 3, 2010 at 11:15

answered May 3, 2010 at 11:03

jensgram

31.6k6 gold badges83 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jensgram Over a year ago

@JasonS You're very welcome. Consider refactoring an ID and "reference name" (e.g., "albums") into the ratings table in order to optimize the query. Also, consider appending , a.id DESC to the ORDER clause to force secondary ordering if there is not (yet) 3 rated albums.

Collectives™ on Stack Overflow

Help with MySQL Join Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related