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.