0

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.

veron
  • 61
  • 3
  • 9
  • 1
    Please don't have your question rely on external sources; it should be self contained. State what your question is, what problems you're having, and what you did to try to fix them. – Carcigenicate Dec 26 '16 at 02:58
  • You find all films in which either 'Crowe' or 'Nolte' (or both) played, not only those were *both* played in. – Thorsten Kettner Dec 26 '16 at 03:08

4 Answers4

1

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;
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
0

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)

sri vatsa
  • 1
0

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
SURIYA PRAKASH
  • 39
  • 4
0

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

gadeatric
  • 1
  • 1