CODEWITHSUNDEEP
mysqlsql
tpace4
tpace4

Reputation: 11

How do I list the titles of films with the fewest rows in the inventory table in sql

This is the problem I am trying to solve:

Refer to the film and inventory tables of the Sakila database. The tables in this lab have the same columns and data types but fewer rows.

Write a query that lists the titles of films with the fewest rows in the inventory table.

This query requires a subquery that computes the minimum of counts by film_id:

SELECT MIN(count_film_id) FROM ( SELECT COUNT(film_id) AS count_film_id FROM inventory GROUP BY film_id ) AS temp_table;

So far the code that I have is:

SELECT title FROM film INNER JOIN inventory ON inventory.film_id = film.film_id WHERE ( SELECT MIN(count_film_id) FROM ( SELECT COUNT(film_id) AS count_film_id FROM inventory GROUP BY film_id ) AS temp_table );

I have tried many different commands in the WHERE statement just before the subquery and I always end up with multiple repeats of the movie title instead of just two different titles. I'm very new to SQL so thanks for all the help.

Upvotes: 1

Views: 5025

Answers (3)

toonice
toonice

Reputation: 2236

The following has been successfully tested against the Sakila database...

SELECT film.title AS Title
FROM film
INNER JOIN
(
    SELECT   film_id AS film_id_for_count,
             COUNT( film_id ) AS count_film_id
    FROM     inventory
    GROUP BY film_id
) AS count_film_id_table ON film.film_id = count_film_id_table.film_id_for_count
INNER JOIN
(
    SELECT MIN( count_film_id_for_min ) AS min_count_film_id
    FROM
    (
        SELECT   film_id AS film_id_for_count_for_min,
                 COUNT( film_id ) AS count_film_id_for_min
        FROM     inventory
        GROUP BY film_id
    ) AS count_film_id_table_for_min
) AS min_count_table ON count_film_id_table.count_film_id = min_count_table.min_count_film_id;

The query starts with the following subquery, which generates a list of Film ID's and associated counts of their rows...

SELECT   film_id AS film_id_for_count_for_min,
         COUNT( film_id ) AS count_film_id_for_min
FROM     inventory
GROUP BY film_id

This is expanded into the following to produce a table containing the minimum value of those counts...

SELECT MIN( count_film_id_for_min ) AS min_count_film_id
FROM
(
    SELECT   film_id AS film_id_for_count_for_min,
             COUNT( film_id ) AS count_film_id_for_min
    FROM     inventory
    GROUP BY film_id
) AS count_film_id_table_for_min

By performing an INNER JOIN of the table containing the list of Film ID's and their counts with the table containing the minimum value we get a list limited to only those Film ID's with the minimum count. By then performing another INNER JOIN on this list with the film table we can get a list of the corresponding film titles.

Sadly, in MySQL subqueries are only visible to their parent (sub)queries, but not to subqueries that are siblings of one of those parents. Thus we have to define the code to generate the list of Film ID's and their count twice.

Upvotes: 0

alowder94
alowder94

Reputation: 41

I actually just came across the same question, here is what I came up with, it passed:

Select film.title
From film
Right Join inventory
On film.film_id = inventory.film_id
Group by film.film_id
Having count(*) =
  ( SELECT MIN(count_film_id) FROM 
      ( SELECT COUNT(film_id) AS count_film_id
        FROM inventory
        GROUP BY film_id ) 
    AS temp_table );

Upvotes: 4

George Sun
George Sun

Reputation: 1080

Welcome to Stack Overflow! It would have been helpful if you had included your table schemas, but I think I was able to understand them by carefully studying your problem and queries.

The following query will display the films ordered from lowest number of inventory entries.

SELECT film.film_id, title, COUNT(inventory.film_id) AS count
FROM inventory
INNER JOIN film
ON inventory.film_id = film.film_id
GROUP BY inventory.film_id
ORDER BY count ASC;

For the following tables with sample entries, the query above returns the following.

mysql> SELECT * FROM film;
+---------+---------+
| film_id | title   |
+---------+---------+
|       1 | Title 1 |
|       2 | Title 2 |
|       3 | Title 3 |
|       4 | Title 4 |
|       5 | Title 1 |
|       6 | Title 2 |
|       7 | Title 3 |
|       8 | Title 4 |
+---------+---------+
8 rows in set (0.04 sec)

mysql> SELECT * FROM inventory;
+--------------+---------+---------+
| inventory_id | film_id | barcode |
+--------------+---------+---------+
|            1 |       1 | fwedqq  |
|            2 |       4 | afwedw  |
|            3 |       1 | vrfefe  |
|            4 |       1 | ngdwee  |
|            5 |       3 | epekdk  |
|            6 |       3 | cwdege  |
|            7 |       2 | vcsaqd  |
|            8 |       3 | phkigk  |
|            9 |       4 | lgjdhd  |
|           10 |       1 | ritiro  |
|           11 |       2 | endnsa  |
+--------------+---------+---------+
11 rows in set (0.00 sec)

mysql> SELECT film.film_id, title, COUNT(inventory.film_id) AS count
    -> FROM inventory
    -> INNER JOIN film
    -> ON inventory.film_id = film.film_id
    -> GROUP BY inventory.film_id
    -> ORDER BY count ASC;
+---------+---------+-------+
| film_id | title   | count |
+---------+---------+-------+
|       4 | Title 4 |     2 |
|       2 | Title 2 |     2 |
|       3 | Title 3 |     3 |
|       1 | Title 1 |     4 |
+---------+---------+-------+
4 rows in set (0.01 sec)

Upvotes: 1

Related Questions