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?

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


| id | movie_id | rating |


| 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!

-------------Problems Reply------------

Use the HAVING clause. Something along these lines:

SELECT, movies.title, COUNT( AS num_ratings
FROM movies
LEFT JOIN ratings ON
HAVING num_ratings > 5;

You'll probably want to use MySQL's HAVING clause

The JOIN method is somewhat stilted and confusing because that's not exactly what it was intended to do. The most direct (and in my opinion, easily human-parseable) method uses EXISTS:

SELECT whatever
FROM movies m
FROM reviews
WHERE movie_id =
HAVING COUNT(*) > xxxxxxxx )

Read it out loud -- SELECT something FROM movies WHERE there EXIST rows in Reviews where the movie_id matches and there are > xxxxxx rows

SELECT * FROM movies
(SELECT movie_id, COUNT(*) as num_ratings from ratings GROUP BY movie_id) as movie_counts
ON = movie_counts.movie_id
WHERE num_ratings > 3;

That will only get you the movies with more than 3 ratings, to actually get the ratings with it will take another join. The advantage of a subquery over HAVING is you can aggregate the ratings at the same time. Such as (SELECT movie_id, COUNT(*), AVG(rating) as average_move_rating ...)

Edit: Oops, you can aggregate with the having method to. :)

The above solutions are okay for the scenario you mentioned. My suggestion may be overkill for what you have in mind, but may be handy for other situations:

  1. Subquery only those from the ratings table having more than the number you need (again using tha group by having clause):

    select movie_id from ratings group by movie_id having count (*) > x

  2. Join that subquery with the movies table

    select from movies join as MoviesWRatings on = MoviesWRatings.movie_id

When you're doing more stuff to the subquery, this might be helpful. (Not sure if the syntax is right for MySQL, please fix if necessary.)

Category:sql Views:2 Time:2008-10-06
Tags: mysql sql

Related post

  • How to create a mysql join query with hierarchical data 2011-03-10

    I need to create a join query for hierarchical data across 2 tables. These tables can have unlimited amounts of data and their structures are as follows: group_id group_name group_order 1 group 1 2 2 group 2 1 field_id field_name parent_group field_o

  • mysql join query using like? 2009-12-18

    I have been trying to get this working for quite a while now but it just doesn't seem to work, maybe it is is not even possible, what i am wanting to do is to perform a mysql join query using like, such as this example i found... SELECT * FROM Table1

  • Which MySQL JOIN query is more efficient? 2009-11-12

    Given the following table structure: CREATE TABLE user ( uid INT(11) auto_increment, name VARCHAR(200), PRIMARY KEY(uid) ); CREATE TABLE user_profile( uid INT(11), address VARCHAR(200), PRIMARY KEY(uid), INDEX(address) ); Which join query is more eff

  • MySQL join query help 2009-01-23

    Basically I have a mysql query something like the following: mysql_query("SELECT n.title, v.value FROM posts n INNER JOIN votes v ON ="); And what I need to do is to grab the title from the posts table and the current vote value from the vo

  • MySQL JOIN Query taking far too long to complete 2009-08-12

    I have a table called users with roughly 250,000 records in it. I have another table called staging with around 75,000 records in it. Staging only has one column, msisdn. I want to check to see how many rows in staging are not present in users. I hav

  • How to write this simple MySQL JOIN query? 2011-03-25

    I need to select records from 2 tables, one called cities and one called neighborhoods. They both share a table column in common called parent_state. In this cell the id of the parent state is stored. I need to select all cities and neighborhoods tha

  • MYSQL Join Query Optimization with Multiple tablesU 2011-10-14

    I have 4 tables Class,Exam,Questions,Answers,UserResponses Class -> ID , Name Exam -> ID,Name,Class_ID_FK Questions -> ID,Question,Exam_ID,FK Answers -> ID,Answer,Question_ID_Fk UserResponses -> ID,UserID,Answer_ID_fk,Question_ID_fk I

  • Replicate MYSQL JOIN query with Zend_Db join() 2012-01-06

    Hi this is my sql query SELECT,,, FROM accounts A LEFT JOIN friends F ON = F.account_id LEFT JOIN accounts ACC ON F.friend_id = LIMIT 0 , 30 How do I replicate this using Zend_Db join? I created this function f

  • Get Data From a temporary Table Created by Equi Join Query 2012-01-25

    i have a table created by an equijoin sql query but i am unable to get data from that table I am fetching data from that table from a query string please check code here <style type="text/css"> a { text-decoration:none; } .Menu { width:150px; f

  • How do I use an IF statement in an MySQL join query? 2012-04-08

    I have a SQL query that left joins a table in different ways depending on a condition. SELECT, u.first_name AS otherUser FROM matches AS m IF ( LEFT JOIN users AS u ON = m.user1ID ELSE LEFT JOIN users AS u ON = m.user2ID

  • Mysql join query retrieving profile images of users with ids from other table 2012-04-09

    I'm having trouble with a join query, my issue is as follows. Table: battles Fields: id,attacker_id,defender_id Table: users Fields: id,profile_image I would like to do a query to retrieve a battle and get the profile images as well from the other ta

  • How do I export a table I created through a Join query in PHPMyAdmin? 2012-04-12

    I entered a join query into PHPMyAdmin and created a table. How do I export it? I seem to only be able to export existing tables, not generated ones. --------------Solutions------------- Not sure which version of PHPMyAdmin (mine's 3.3.9) you're usin

  • mysql join query returns data repeatedly 2014-02-20

    i want to retrieve data from mysql database (using join query) in c# windows form but it gives me the data repeatedly 100 times. This is my code. please help. private void button2_Click(object sender, EventArgs e) { if (comboBox1.SelectedIndex < 0

  • Mysql join query for multiple "tags" (many-to-many relationship) that matches ALL tags? 2010-07-16

    I am trying to query for Objects that match ALL of a given set of Tags. Basically I want users to be able to add on more and more Tags to filter or "narrow down" their search results, kind of like does. My table structure is a table of Obj

  • Need help with a mysql join query 2010-12-20

    I have a BOOK table that stores books, and a SIMI_BOOK table that stores the similarity values of any 2 books. Below are the keys used by the 2 tables. BOOK: bid btitle SIMI_BOOK: bid1 bid2 similarity I need a join query that could fetch all the info

  • PHP MYSQL Join Query. SELECT WHERE AND IN OR Logic Error 2011-03-03

    Hey all. I think I have a logic error in my qry. The output is correct but in triplets. I’ve been staring at this for a long time and not seeing it. Can someone shed some light on this? Thanks!! Also wanted to add this info as well. $userid = 1 $User

  • Displaying results after MySQL JOIN query with PHP 2011-06-08

    $sql = "SELECT messages.text, FROM messages INNER JOIN users ON ORDER BY messages.ms_id DESC LIMIT 10"; $result = mysql_query($sql); $rows = array(); while($row = mysql_fetch_array($result)) { $rows[]=$row; } echo

  • mysql join query to extract ids which are blocking only CLOSED bugs 2012-03-06

    Tables bugs and dependencies looks like this: select * from bugs; +--------+--------+ | bug_id | status | +--------+--------+ | 20 | NEW | | 45 | CLOSED | | 47 | NEW | | 30 | NEW | | 50 | CLOSED | +--------+--------+ select * from dependencies; +----

  • need help in mysql join query 2011-06-19

    I am working on a website where i have three tables 1:- Book Author 2:- Book type 3:- Book relationship My first table have following fields ------------------- id Author_name 1 abc 2 xyz ------------------ Second table id book_type 1 Politics 2 Reli

Copyright (C), All Rights Reserved.

processed in 0.093 (s). 11 q(s)