CODEWITHSUNDEEP
sqlrelational-database
veron
veron

Reputation: 61

Relational division: Find all movies two actors cast in together

Problem:

Given film_actor and film tables from the DVD Rental sample database find all movies both Sidney Crowe (actor_id = 105) and Salma Nolte (actor_id = 122) cast in together and order the result set alphabetically.

My solution:

SELECT DISTINCT f.title
FROM film f
         INNER JOIN film_actor a ON
    f.film_id = a.film_id
         INNER JOIN actor c ON
    a.actor_id = c.actor_id
WHERE c.last_name IN ('Crowe', 'Nolte')
GROUP BY f.title;

I'm trying to figure why the solution above is not working.

Upvotes: 0

Views: 2713

Answers (4)

gadeatric
gadeatric

Reputation: 1

i might be late, but i found this solution is easier than the mentioned in this thread. posting bc it might help the others who dared to train with this kata:

SELECT DISTINCT f.title
FROM film f 
  INNER JOIN film_actor fa ON fa.film_id = f.film_id
WHERE fa.actor_id = 105 OR fa.actor_id = 122
GROUP BY 1
having count(distinct fa.actor_id) = 2

reasoning:

  1. the 'actor' table is not neccesary, since actor_id is already in the film_actor schema.
  2. the key is in the having! you need to imply that only the results with both actors (aka with 2) are wanted. once you specify this condition, combined with the WHERE clause, only the movies with those actors will be displayed

hope it helped :D

Upvotes: 0

SURIYA PRAKASH
SURIYA PRAKASH

Reputation: 39

Basically we need to have the count above 2 for both of them in same film (film_id) to conclude that they have cast in together for same movie. So group by film_id and filter with count 2.

Viola !!!

Hope this helps you guys :)

select film.title from 
(select film_id, count(film_id) as cnt from film_actor 
where actor_id in (105, 122)
group by film_id
) temp
inner join film
on temp.film_id = film.film_id
where cnt=2
order by title asc

Upvotes: 0

sri vatsa
sri vatsa

Reputation: 1

Since you are using c.lastName IN('Crowe', 'Nolte') you are displaying all the films these two were part of but the question is both these actors must be part of that film so you need to use c1.lastName=('Crowe') and c2.lastName=('Nolte)

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

I think actor IDs are already given. Also, you have to join film_actors twice.

Select f.title
From film f
Inner join film_actor a1
On f.film_id = a1.film_id
Inner join film_actor a2
On f.film_id = a2.film_id
Where a1.actor_id = 105
And a2.actor_id = 122
Order by f.title;

Upvotes: 1

Related Questions