How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table?

Question

I have two tables in my database, called ratings and movies.

Ratings:

| id | movie_id | rating |

Movies:

| id | title |

A typical movie record might be like this:

| 4 | Cloverfield (2008) |

and there may be several rating records for Cloverfield, like this:

| 21 | 4 | 3 | (rating number 21, on movie number 4, giving it a rating of 3)

| 22 | 4 | 2 | (rating number 22, on movie number 4, giving it a rating of 2)

| 23 | 4 | 5 | (rating number 23k on movie number 4, giving it a rating of 5)

The question:

How do I create a JOIN query for only selecting the rows in the movie table that have more than x number of ratings in the ratings table? For example, in the above example if Cloverfield only had one rating in the ratings table and x was 2, it would not be selected.

Thanks for any help or advice!

Answer 1

Use the HAVING clause. Something along these lines:

SELECT movies.id, movies.title, COUNT(ratings.id) AS num_ratings 
  FROM movies 
  LEFT JOIN ratings ON ratings.movie_id=movies.id 
  GROUP BY movies.id 
  HAVING num_ratings > 5;