123 123 - 1 month ago 9
MySQL Question

SQL - Find the film title and language name of all films in which ADAM GRANT acted

I'm having a ton of trouble thinking in terms of SQL on problems like this. I don't know how I should be structuring my queries. Should I be joining

film
on
film_actor
and then join that on
actor
? Or should I do it the other way around?



All I've been able to do is get a query that joins
actor
and
film_actor
with the name
ADAM GRANT
but I'm lost on where to go from here.

SELECT * FROM (actor JOIN film_actor ON actor.actor_id=film_actor.actor_id) WHERE first_name = 'ADAM' AND last_name = 'GRANT'


I've tried doing something to join these results with films, but I'm getting issues with my syntax.

SELECT title FROM (actor JOIN film_actor ON actor.actor_id=film_actor.actor_id) WHERE first_name = 'ADAM' AND last_name = 'GRANT' JOIN film ON film.actor_id = actor_id

Answer

You should use inner join

SELECT 
   film.title
  , language.name
FROM film
INNER JOIN language on film.language_id = language.language_id
INNER JOIN film_actor on film.film_id = film_actor.film_id
INNER JOIN actor on actor.actor_id = film_actor.actor_id
WHERE actor.first_name = 'ADAM'
AND actor.last_name ='GRANT'