CODEWITHSUNDEEP
mysqlsql
user7441072
user7441072

Reputation: 279

mysql query to find actors acted in both films

am trying to display actors acted in both films please help,it is not getting

SELECT actors.first_name,actors.last_name
FROM actors
LEFT JOIN films_actors
  ON actors.actor_id=films_actors.actor_id
LEFT JOIN films
  ON films_actors.film_id=films.film_id
WHERE films.title="ACADEMY DINOSAUR"
  AND films.title="ANACONDA CONFESSIONS"

Upvotes: 1

Views: 89

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use HAVING COUNT(DISTINCT ...)

SELECT actors.first_name,actors.last_name 
FROM actors 
JOIN films_actors 
  ON actors.actor_id=films_actors.actor_id
JOIN films 
  ON films_actors.film_id=films.film_id 
WHERE films.title IN ('ACADEMY DINOSAUR','ANACONDA CONFESSIONS')
GROUP BY actors.first_name,actors.last_name 
HAVING COUNT(DISTINCT films.title) = 2;

Upvotes: 3

Related Questions