1
(
 Select Movie_Name
 From Theatres Inner Join Movies
 On Theatre_Movie_ID = Movie_ID AND Showing_Time = '9:00PM'
) 

INTERSECT 

( 
 Select Movie_Name
 From Movies
 Where Duration = '2:20' 
)
GSerg
  • 76,472
  • 17
  • 159
  • 346
Jacob Berger
  • 11
  • 2

1 Answers1

1

As suggested by @jason-goemaat above. Since there's already a JOIN with Movies we can add a WHERE clause for the Duration:

 Select Movie_Name
 From Theatres Inner Join Movies
 On Theatre_Movie_ID = Movie_ID
 Where Movies.Duration = '2:20' AND Theatres.Showing_Time = '9:00PM'
karmakaze
  • 34,689
  • 1
  • 30
  • 32
  • Not quite the same semantics as the original. If there are multiple movies of the same name – Martin Smith Dec 31 '19 at 16:53
  • If you want unique movie names add `DISTINCT` as in `Select DISTINCT Movie_Name`... – karmakaze Dec 31 '19 at 16:59
  • That isn't the issue. There are two different movies with the name "Bad Boys" - 1983 and 1985. The query in the question will return the name "Bad Boys" if one of the movies has a duration of '2:20' and the other move is showing in theatres at 9PM. The proposed rewrite won't return it in this case - hence not quite same semantics – Martin Smith Dec 31 '19 at 17:07
  • 1
    Yes right. Looking at the implications I suspect that this is more desirable in a real world scenario than the original output. – karmakaze Dec 31 '19 at 18:12