SQL Server Intersection of Records from Single Table

Question

I have the following table:

|  TheaterID  |   MovieID   |
|-------------|-------------|
|     1001    |     201     |
|     1001    |     202     |
|     1001    |     203     |
|     1001    |     206     |
|     1006    |     201     |
|     1006    |     203     |
|     1006    |     206     |
|     1011    |     201     |
|     1011    |     203     |
|     1011    |     204     |
|     1011    |     206     |
|     1039    |     201     |
|     1039    |     202     |
|     1039    |     203     |
|     1039    |     204     |
|     1039    |     206     |

And I have a list containing the following records of TheaterID:

|  TheaterID  |
|-------------|
|     1001    |
|     1006    |
|     1008    |
|     1011    |
|     1039    |

I'm trying to get a single list containing the intersection of MovieIDs from the main table.

|   MovieID   |
|-------------|
|     201     |
|     203     |
|     206     |

What would be the most efficient way to do this?

Answer 1

One option would be a join followed by an aggregation:

SELECT m.MovieID
FROM Movies m
INNER JOIN Theaters t
    ON m.TheaterID = t.TheaterID
GROUP BY
    m.MovieID
HAVING
    COUNT(*) = (SELECT COUNT(DISTINCT t.TheaterID)
                FROM Theaters t INNER JOIN Movies m
                    ON t.TheaterID = m.TheaterID);

Demo

The strategy here is to aggregate over each movie and assert that its count, which should be the number of theaters, matches the total number of theaters which overlap between the two tables. The somewhat complex-looking HAVING clause is what handles this.