Misohappi Misohappi - 1 year ago 67
SQL Question

SQL query return name and count of specific attributes across multiple tables

Given multiple tables I'm trying to write a query that returns the names that satisfies a specific count clause.

I have the tables"
genre(genre, movieid)
moviedirectors(movieid, directorid)
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

I believe this should be correct but when I run this query on the command line it never finishes. I waited 30 minutes and got no results.

Any advice would be appreciated.

Answer Source

you need inner joins:

SELECT d.fname
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download