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

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

CodePudding user response：

The bridge table videogames2developers suffices for this task. First get all video games with more than five developers, then count how many these are.

select count(*)
from
(
  select videogameid
  from videogames2developers 
  group by videogameid
  having count(*) > 5
) games_with_more_than_five_devs;

This assumes that there is one entry per game and developer in the table. If this is not the case, then replace having count(*) > 5 by having count(distinct developerid) > 5.

CodePudding user response：

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