0

I would like to return a set of move titles that both actors appear in. I have searched and tried to be creative with the value input into the outer query's SELECT statement. I can confirm that each sub-query returns the correct values respectively. 

    SELECT DISTINCT z.title 
    FROM (SELECT film.title FROM film INNER JOIN film_actor ON          
    film.film_id=film_actor.film_id INNER JOIN actor ON         
    film_actor.actor_id=actor.actor_id WHERE actor.first_name='KIRSTEN' AND 
    actor.last_name='PALTROW')AS z 
    INNER JOIN (SELECT film.title FROM film INNER JOIN film_actor ON        
    film.film_id=film_actor.film_id INNER JOIN actor ON 
    film_actor.actor_id=actor.actor_id WHERE actor.first_name='WARREN' AND 
    actor.last_name='NOLTE')AS x;

Naturally I want to do 'SELECT DISTINCT film.title...' in the outer query. This does not work because the table 'film' does not exist in the outer query but only 'z' and 'x' do correct? How may I return set of values that is found in both subqueries?

dclayton12
  • 1
  • 2
  • Do you mean [`INTERSECT`](http://www.tutorialspoint.com/sql/sql-intersect-clause.htm)? – PM 77-1 Feb 17 '15 at 02:18
  • Typo in my thread title. How else can I accomplish this as mysql does not support INTERSECT as stated in the documentation? – dclayton12 Feb 17 '15 at 03:13
  • See [Alternative to Intersect in MySQL](http://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql) – PM 77-1 Feb 17 '15 at 03:17

0 Answers0