Can't solve this SQL query involving subqueries for given film database

Question

Relational Schema

Given the relational schema above, I cannot seem to figure out how to solve this query:

8. Write an SQL query to find the film titles that all stores carry (i.e., in all store’s inventories). Assume there can be any number of stores (i.e., you cannot assume a certain number of stores). Your query also cannot use COUNT(). (Hint: it isn’t difficult using subqueries to find stores that don’t have a particular film.)

Answer 1

Lets just define a simplified version of the schema you suppled with only the tables & columns we will need, and some representative data to work with.

(for future reference, you can "help us to help you" by doing this step for us. It also shows that you have given some thought to what the problem involves)

DECLARE @t_film TABLE
(   film_id INT)

DECLARE @t_store TABLE
(   store_id INT)

DECLARE @t_inventory TABLE
(   inv_id INT IDENTITY,
    store_id INT,
    film_id INT
)

 INSERT INTO @t_film
 VALUES (1),
        (2),
        (3),
        (4),
        (5),
        (6)

 INSERT INTO @t_store
 VALUES (1),
        (2),
        (3),
        (4)

 INSERT INTO @t_inventory
 VALUES (1,1),
        (1,3),
        (1,4),
        (1,5),
        (2,1),
        (2,4),
        (2,5),
        (3,1),
        (3,2),
        (3,3),
        (3,5),
        (4,1),
        (4,2),
        (4,3),
        (4,4),
        (1,6),
        (2,6),
        (3,6),
        (4,6)
-- Every store has films 1 & 6

Now lets start with the hint they give you and take it from there. "use subqueries to find stores that don’t have a particular film."

-- First create a list of every store as if it had every film
SELECT *
  FROM @t_film f
 CROSS JOIN @t_store s
--Then compare that to the actual inventory of each store
  LEFT JOIN @t_inventory i ON f.film_id = i.film_id
                          AND s.store_id = i.store_id
--Then filter that list to only the films which dont appear in a particular store.
 WHERE i.inv_id IS NULL

Then we can just select all films which dont appear in that list. We turn the query above into a subquery and use it to filter our results.

SELECT *
  FROM @t_film r
 WHERE film_id NOT IN (SELECT f.film_id
                         FROM @t_film f
                        CROSS JOIN @t_store s
                         LEFT JOIN @t_inventory i ON f.film_id = i.film_id
                                                 AND s.store_id = i.store_id
                        WHERE i.inv_id IS NULL)

There is likely a more elegant way to do this but I think this approach is easiest to follow the logic of. For example instead of using NOT IN you could use another LEFT JOIN with a NULL check to achieve the same thing.

SELECT r.*
  FROM @t_film r -- result
  LEFT JOIN (SELECT f.film_id
               FROM @t_film f
              CROSS JOIN @t_store s
               LEFT JOIN @t_inventory i ON f.film_id = i.film_id
                                       AND s.store_id = i.store_id
              WHERE i.inv_id IS NULL) inv ON r.film_id = inv.film_id
 WHERE inv.film_id IS NULL

The point is to understand the logic. From there you can find any number of solutions.