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?

Tim Biegeleisen · Accepted Answer · 2019-08-27 16:35:22Z

3

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.

edited Aug 27, 2019 at 16:35

answered Aug 27, 2019 at 16:28

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Smiley Over a year ago

Thank you! This is exactly what I needed. Also big thanks to that demo explanation. Makes perfect sense!!!

Collectives™ on Stack Overflow

SQL Server Intersection of Records from Single Table

1 Answer 1

Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related