Given multiple tables I'm trying to write a query that returns the names that satisfies a specific count clause.
I have the tables"
directors(directorid, firstname, lastname)
I want to write a query that returns the first and last name of directors that directed at least 50 movies of the genre comedy, and return that number as well.
This is what I have
select d.fname, d.lname, count(*)
from genre g, directors d, moviedirectors md
where g.genre='Comedy' and g.movieid=md.movieid and
group by d.id
having count(*) >= 50
you need inner joins:
SELECT d.fname d.lname FROM genre g INNER JOIN moviedirectors md ON g.movieid = md.movieid INNER JOIN directors d ON md.directorid = d.directorid WHERE g.genre = 'Comedy' GROUP BY d.fname, -- group by columns in select d.lname HAVING COUNT(*) >= 50