I’m trying to list the actors (male/female) that have starred in 10 or more different film genres I have a table called actors which contains three columns:actorID,name,gender and another table called movies which contains movieID,title,year, last table is movies_actor it contains movieID,actorID,as_charachter,leading
Asked
Active
Viewed 292 times
-2
THeHac101
- 7
- 4
2 Answers
0
To get the count of movie for each actor You need the actor_name and the movieid which are in two different table. First you can take the actorid and the count of movieid from movies_actors table and the join it with the actor_table to get the name of the actor. You have to apply a condition in select statement >9 as you want the actor name who has done 10 or more than 10 movies. For genres you can try once from your end if you get any error then you can ask question.
select name from
(select s.*,a.name from
(select actorid, count(distinct movieid)no_of_movies from movies_actors group by actorid)s
join
actor_table a
on a.actorid=s.actorid
)b
where no_of_movies>9
But It will be good for you if you try first from your end and then you can ask questions here like if you stuck or if you get any error or not get the desired output. In this way you can learn more.
Jaishree Rout
- 382
- 5
- 17
-
its not working – THeHac101 Feb 18 '20 at 07:22
-
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(distinct movieid)no_of_movies from imdb_movies2actors group by actorid)s ' at line 3 – THeHac101 Feb 18 '20 at 07:34
-
Hi Can you retry. There was a comma missing. I have added – Jaishree Rout Feb 18 '20 at 07:50
-
thanks but can you explain the code please – THeHac101 Feb 18 '20 at 07:53
-
also the question was List any actors (male/female) that have starred in 10 or more different film genres. – THeHac101 Feb 18 '20 at 07:56
-
i forget to add one more table i will edit above can you re check ? – THeHac101 Feb 18 '20 at 07:59
-
I have added the description. But please try from your end once for genres. – Jaishree Rout Feb 18 '20 at 09:11
0
Check whether below query give your expected result.
SELECT
a.name,
a.gender,
COUNT(ac.movieid) NoOf_Movies
FROM
actors a
INNER JOIN actor_movie am ON a.id = am.actorID
GROUP BY
a.name,
a.gender
HAVING
COUNT(ac.movieid) > 9
ORDER BY
am.movieID DESC
Tharuka Madumal
- 201
- 2
- 5
-
-
is there any error message ? or is this query don't give any output? – Tharuka Madumal Feb 18 '20 at 07:24
-
-
-
Please check whether table name and column names are correct. In your previous query your table name was actor_movie but in the question your table name is movies_actor. So replace the correct table names and column names and try again – Tharuka Madumal Feb 18 '20 at 07:29
-
even tho the tables are correct am getting this : Unknown column 'ac.movieid' in 'field list' – THeHac101 Feb 18 '20 at 07:32
-
-
Well there is no `ac` table in the `FROM` clause. I assume it needs to be `am.movieid`. – TiiJ7 Feb 18 '20 at 07:39
-
-
-
-
Can I have your tables names and column names please? Then I will modify the query according to that – Tharuka Madumal Feb 18 '20 at 08:06
-
i have a table called actors which contains three columns:actorID,name,gender and another table called movies which contains movieID,title,year, last table is movies_actor it contains movieID,actorID,as_charachter,leading and another table called movie director which had movieid directorid and genre – THeHac101 Feb 18 '20 at 15:09
-
I’m trying to list the actors (male/female) that have starred in 10 or more different film genres , and here is what am trying to do – THeHac101 Feb 18 '20 at 15:10