How to solve the following case?

Question

Consider the following question: We have 3 tables,

1. Theatre  ( theatre_Id, thatre_name )
2. ShowTime ( showTimeId,  theatre_Id, movie_id )
3. Movies   ( movie_id, movie_name )

Now same movie name can also have different movieId's sort of dependent on the reel.

Eg: [1, HarryPotter], [2, HarryPotter], [3, Pirates of Carr]

Now we need to find movie name which has showtime on all theatre locations ? Is it nested correlated query ?

Answer 1

If you better phrase the question:

What are the names of movies whose theatre list is the same size as the number of theatres?

you get the query:

select distinct movie_name
from Movies m
where movie_id in (
    select movie_id
    from ShowTime
    group by movie_id
    having count(distinct theatre_Id) = (select count(*) from Theatre))