I want to join 2 of this result-CodePudding

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;

CodePudding user response：

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;

CodePudding user response：

You can use EXISTS for the second condition, but it would be better to have a unique identifier for the actrs, as there could be more than one actor in two movies with the same 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' AND EXISTS
(
SELECT A.first_name, A.last_name, F.title FROM actor AS A1
    JOIN film_actor AS Fa ON A1.actor_id = Fa.actor_id
    JOIN film AS F ON F.film_id = Fa.film_id 
WHERE F.title = 'JASON TRA AND A.first_name 
= A1.first_name AND  A.last_name  A1.last_name)
ORDER BY A.first_name;

CodePudding user response：

Thank you y'all it's work now , with this SQL Statement i mixed of your SQL Code now it's look like this

SELECT A.first_name, A.last_name 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

EXISTS 
(
    SELECT A.first_name, A.last_name FROM actor AS A2
        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'
)
;

but now i want to show title of two movie on one table i try to use alias but look like it's not work can you guide me a little bit more ?

SELECT A.first_name, A.last_name, F.title AS Title1, Title2 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

EXISTS 
(
    SELECT A.first_name, A.last_name, F.title AS Title2 FROM actor AS A2
        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'
) AS Movie2Filter
;