How do I query this many-to-many in SQL

Question

I have these tables:

• Director (director_id, director_name)
• Film (film_id, title, year, duration, director_id)
• Participation (act_id, film_id, salary)
• Actor (act_id, act_name)

I want to display the names of the actors who participated in films directed by "xxxx" or "yyyy", and have never participated in films directed by "zzzz"

Is this a correct way?

select act_name
from Actor
where act_id in (select p.act_id
                 from Participation
                 where p.film_id in (select f.film_id
                                     from Film
                                     where director_id in (select director_id
                                                           from Director
                                                           where director_name = 'xxxx' OR director_name = 'yyyy' AND director_name <> 'zzzz');

Answer 1

You were quite close. See this :

SELECT actorInfo.Act_Id, actorInfo.Act_Name
FROM Actor actorInfo
WHERE actorInfo.Act_Id IN (SELECT Act_Id
                           FROM Participation
                           WHERE Film_Id IN (SELECT Film_Id
                                             FROM Film
                                             WHERE Director_Id IN (SELECT Director_Id
                                                                                        FROM Director
                    WHERE Director_Name IN ('xxxx', 'yyyy'))
                      AND Director_Id NOT IN (SELECT        Director_Id
                                              FROM Director
                                              WHERE Director_Name IN ('zzzz'))))

Another way of writing the same with somewhat lesser sub-queries would be :

SELECT actorInfo.Act_Id, actorInfo.Act_Name
FROM Actor actorInfo
INNER JOIN Participation participation ON participation.Act_Id = actorInfo.Act_Id
WHERE participation.Film_Id IN (SELECT Film_Id
                                FROM Film
                                WHERE Director_Id IN (SELECT    Director_Id
                                                      FROM Director
                                                      WHERE Director_Name IN ('xxxx', 'yyyy')))
  AND participation.Film_Id NOT IN (SELECT Film_Id
                                    FROM Film
                                    WHERE Director_Id IN (SELECT Director_Id
                                                          FROM Director
                                                          WHERE Director_Name NOT IN ('zzzz')))