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?

mysql, sql

Solution

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;

Problem

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!

Original source