CODEWITHSUNDEEP
mysql
Stanley Ngumo
Stanley Ngumo

Reputation: 4239

Selecting rows from one table using values gotten from another table MYSQL

I have currently have 2 mysql tables in my db

Film and Film_Ratings_Report

The primary key for Film is filmid which is used to identify the film ratings in the Film_Ratings_Report table.

I would like to know if its possible using a MYSQL query only to search the ratings table and collect all film ids which fit a certain criteria then use the selected IDs to get the film titles from the Film table. Below is the MYSQL query Im using which isnt working:

SELECT * 
FROM film 
UNION SELECT filmid 
      FROM film_rating_report 
      WHERE rating = 'GE' 
      LIMIT 0,0

I am relatively green to MYSQL and would appreciate any help on this.

Thanks in Advance

Upvotes: 16

Views: 44892

Answers (5)

Tomas
Tomas

Reputation: 59485

Preferred solution for this is to use join, and don't forget group by so that you don't have duplicate lines:

select film.*
from film
join film_rating_report on film.filmid = film_rating_report.filmid
        and rating = 'GE'
group by film.filmid

EDIT: as correctly noted by @ypercube, I was wrong claiming that the performance of join & group by is better than using subqueries with exists or in - quite the opposite.

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

It seems you want a semi-join, e.g. a join where only data from one of the 2 joined tables are needed. In this case, all rows from film for which there is a matching row in film_rating_report that has the wanted condition (rating = 'GE').

This is not exactly equivalent to a usual join because even if there are 2 (or more) row in the second table (2 ratings of a film, both with 'GE'), we still want the film to be shown once, not twice (or more times) as it would be shown with a usual join.

There are various ways to write a semi-join and most popular are:

  • using an EXISTS correlated subquery (@Justin's answer):

    SELECT t1.* 
FROM film t1 
WHERE EXISTS (SELECT filmid 
              FROM film_rating_report t2
              WHERE t2.rating = 'GE'
              AND t2.filmid = t1.id);

  • using an IN (uncorrelated) subquery (@SG 86's answer):
    (this should be used with extreme care as it may return unexpected results - or none at all - if the joining columns (the two filmid in this case) are Nullable)

    SELECT * 
FROM film 
WHERE id IN 
  ( SELECT filmid 
    FROM film_rating_report 
    WHERE rating = 'GE'
  );

  • using a usual JOIN with a GROUP BY to avoid the duplicate rows in the results (@Tomas' answer):
    (and note that this specific use of GROUP BY works in MySQL only and in recent versions of Postgres, if you ever want to write a similar query in other DBMS, you'll have to include all columns: GROUP BY f.filmid, f.title, f.director, ...) 

    SELECT f.*
FROM film AS f
    JOIN film_rating_report AS frr
         ON f.filmid = frr.filmid
WHERE frr.rating = 'GE' 
GROUP BY f.filmid ;

  • A variation on @Tomas'es answer, where the GROUP BY is done on a derived table and then the JOIN: 

    SELECT f.*
FROM film AS f
    JOIN 
        ( SELECT filmid
          FROM film_rating_report
          WHERE rating = 'GE'
          GROUP BY filmid
        ) AS frr
      ON f.filmid = frr.filmid ;

Which one to use, depends on the RDBMS and the specific version you are using (for example, IN subqueries should be avoided in most versions of MySQL as they may produce inefficient execution plans), your specific table sizes, distribution, indexes, etc.

I usually prefer the EXISTS solution but it never hurts to first test the various queries with the table sizes you have or expect to have in the future and try to find the best query-indexes combination for your case.

Addition: if there is a unique constraint on the film_rating_report (filmid, rating) combination, which means that no film will ever get two same ratings, or if there is an even stricter (but more plausible) unique constraint on film_rating_report (filmid) that means that every film has at most one rating, you can simplify the JOIN solutions to (and get rid of all the other queries):

    SELECT f.*
    FROM film AS f
        JOIN film_rating_report AS frr
             ON f.filmid = frr.filmid
    WHERE frr.rating = 'GE' ;

Upvotes: 16

Noam Rathaus
Noam Rathaus

Reputation: 5598

I believe this will work, thought without knowing your DB structure (consider giving SHOW CREATE TABLE on your tables), I have no way to know for sure:

SELECT film.*
FROM (film)
LEFT JOIN film_rating_report ON film.filmid = film_rating_report.filmid AND film_rating_report.rating = 'GE'
WHERE film_rating_report.filmid IS NOT NULL
GROUP BY film.filmid

(The WHERE film_rating_report.filmid IS NOT NULL prevents lines that don't have the rating you are seeking from sneaking in, I added GROUP BY at the end because film_rating_report might match more than once - not sure as I have visibility to the data stored in it)

Upvotes: -1

Justin
Justin

Reputation: 9724

Query:

SELECT t1.* 
FROM film t1 
WHERE EXISTS (SELECT filmid 
              FROM film_rating_report t2
              WHERE t2.rating = 'GE'
              AND t2.filmid = t1.id);

Upvotes: 1

SG 86
SG 86

Reputation: 7078

SELECT * FROM film WHERE id IN 
  (SELECT filmid FROM film_rating_report WHERE rating = 'GE');

should work

Upvotes: 16

Related Questions