Issue

This Content is from Stack Overflow. Question asked by r3tr0penguin

I want to query the actor whos play both movie RANDOM GO and JASON TRAP

but it look like i can’t just use AND in my SQL Statement

Please Advise Me.

SELECT A.first_name, A.last_name, F.title FROM actor AS A
    JOIN film_actor AS Fa ON A.actor_id = Fa.actor_id
    JOIN film AS F ON F.film_id = Fa.film_id 
WHERE F.title = 'RANDOM GO' AND F.title ='JASON TRAP'
ORDER BY A.first_name;


SELECT A.first_name, A.last_name, F.title FROM actor AS A
    JOIN film_actor AS Fa ON A.actor_id = Fa.actor_id
    JOIN film AS F ON F.film_id = Fa.film_id 
WHERE F.title = 'RANDOM GO' 
ORDER BY A.first_name;

SELECT A.first_name, A.last_name, F.title FROM actor AS A
    JOIN film_actor AS Fa ON A.actor_id = Fa.actor_id
    JOIN film AS F ON F.film_id = Fa.film_id 
WHERE F.title = 'JASON TRAP' 
ORDER BY A.first_name;

Solution

Check if it solve your problem:

SELECT A.first_name, A.last_name
FROM actor AS A
WHERE EXISTS (SELECT *
              FROM film_actor AS Fa
                   JOIN film AS F ON F.film_id = Fa.film_id
              WHERE A.actor_id = Fa.actor_id AND F.title = 'RANDOM GO') --
      AND EXISTS (SELECT *
                  FROM film_actor AS Fa
                       JOIN film AS F ON F.film_id = Fa.film_id
                  WHERE A.actor_id = Fa.actor_id AND F.title = 'JASON TRAP')
ORDER BY A.first_name;

This Question was asked in StackOverflow by r3tr0penguin and Answered by Meyssam Toluie It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.