How do I find the number of video games with more than 5 developers. (SQL query)

Question

Lets say i have 2 tables which are videogames and developers.

Under the videogames table, we have the following attributes:

• videogameid(PK)
• title
• dateOfRelease

Under the developer table, we have attributes:

• developerid(PK)
• name
• position

I tried finding the total number of videogames that had more than 5 developers. Here is my attempt to resolve the issue.

SELECT COUNT(videogameid)
FROM videogames, developers
WHERE videogameid = developerid
GROUP BY developerid
HAVING COUNT(developerid) > 5; 

While I did not get any error issues but no results popped up.The result was null and not even zero.Hence i might have done something wrong.

EDIT: Yes there is one more table that actually relates to them it was videogames2developers which has the following attributes:

-videogameid
-developerid
-genre

Answer 1

You need to join videogames2developers with videogames and developers table:

select count(videogameid) 
from
    (SELECT v.videogameid
    FROM videogames v inner join videogames2developers vd
         on v.videogameid =vd.videogameid 
                      inner join developers d
         on vd.developerid=d.developerid
    GROUP BY v.videogameid
    HAVING COUNT(d.developerid) > 5
) t