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.

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!

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

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;

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

http://www.severnsolutions.co.uk/twblog/archive/2004/10/03/havingmysql

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
WHERE EXISTS( SELECT COUNT(*)
FROM reviews
WHERE movie_id = m.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
INNER JOIN
(SELECT movie_id, COUNT(*) as num_ratings from ratings GROUP BY movie_id) as movie_counts
ON movies.id = 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 movies.id from movies join as MoviesWRatings on movies.id = 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 n.id = v.id"); 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 A.id, A.name, ACC.id, ACC.name FROM accounts A LEFT JOIN friends F ON A.id = F.account_id LEFT JOIN accounts ACC ON F.friend_id = ACC.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 m.id, u.first_name AS otherUser FROM matches AS m IF (u.id=m.user2ID) LEFT JOIN users AS u ON u.id = m.user1ID ELSE LEFT JOIN users AS u ON u.id = 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 newegg.com 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, users.name FROM messages INNER JOIN users ON messages.user_id=users.id 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) dskims.com, All Rights Reserved.

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