-1

I am trying to display the movies the actors starred in using the Sakila Database.

With this query:

$outerQuery = 
"SELECT concat(a.last_name, ', ',a.first_name) as Actor, count(fa.actor_id) as Num_Films, f.title as Movie
FROM actor as a, film f
INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
INNER JOIN film_actor fa1 ON fa.film_id = f.film_id
GROUP BY fa.actor_id 
ORDER BY a.last_name;";

And I'm getting this output: 

Unknown column 'a.actor_id' in 'on clause'.

Please help I've had so many errors with this part. I'm able to display the Actors names in order and how many movies they appeared in, but not able to display those certain movies the actors starred in.

GMB
  • 216,147
  • 25
  • 84
  • 135
Jonathan Mensi
  • 33
  • 7

1 Answers1

1

As much as I understand you want to get number of films for each actors. I think you have actor, film and film_actor tables and film_actor table has link between actor and film tables. So; You don't need to JOIN second time the film_actor table for your purpose. Also, You have missed to link between film and film_actor tables. GROUP BY was wrong. You need to put not aggregated columns to the GROUP BY (only if you used them in SELECT). If you can provide your table structure we can answer better. With the information you provide I believe this following query can help you :

SELECT    concat(a.last_name, ', ',a.first_name) as Actor
        , count(fa.actor_id) as Num_Films
        , f.title as Movie
FROM actor as a
INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
INNER JOIN film f ON fa.film_id = f.film_id
GROUP BY concat(a.last_name, ', ',a.first_name),f.title
ORDER BY a.last_name;

EDIT : If you want to get number of movies for each actors you shouldn't add film.title to the select, otherwise you will get 1 as number of films for each line. I excluded the film information from the query : 

SELECT    concat(a.last_name, ', ',a.first_name) as Actor
        , count(fa.actor_id) as Num_Films
        --, f.title as Movie
FROM actor as a
INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
--INNER JOIN film f ON fa.film_id = f.film_id
GROUP BY concat(a.last_name, ', ',a.first_name)
         --,f.title
ORDER BY a.last_name;
Zeki Gumus
  • 1,484
  • 7
  • 14