Way to find video games with the most developer involvement (using SQL query)

Question

Suppose we have 3 tables, namely videogames, developers and workson.

In the videogames table, we have the following attributes:

• videogameid (primary key)
• Title
• Year
• genre

Then in the developers table, we have:

• developerid (primary key)
• Name
• gender

Then in the workson table, we have:

• videogameid (primary and foreign keys)
• developerid (primary and foreign keys)

My code attempt:

SELECT MAX(videogameid)
FROM
(SELECT videogameid
FROM workson
GROUP BY videogameid
HAVING COUNT(DISTINCT developerid)>5
)videogames_with_most_developers;

However, I failed to retrieve answers with titles (mainly because I didn't select a title), but that's because I can't seem to make the connection.

Edit: We have some sample data, in the videogames table:

Insert video game (videogameid, title, year, type)

VALUES (111,'World of Warcraft',2004,'MMORPG'); Insert video game (video game id, title, year, type) VALUES (112,'StarCraft II',2008,'RTS');

The developers table contains:

Insert developer (developer ID, gender, name) value(98734,'M','Johnson'); Insert developer (developer ID, gender, name) value(98735,"F","Regina"); Insert developer (developer ID, gender, name) value(98736,"M","Lamar");

The workson table contains:

Insert workson(videogameid,developerid) value (111, 98734); insert workson(videogameid,developerid) Values ​​(111, 98735); insert workson(videogameid,developerid) Value (112, 98736);

The expected output should be the one titled 'World of Warcraft' because it has the most directors working on this project with a count of 2, while the project titled 'Starcraft 2' does not have the most developers in this sample data .

Answer 1

The columns/aggregation functions (MAX, COUNT, AVG, etc.) selected in the select query will be displayed as a table. In your query:

SELECT MAX(videogameid) FROM ....

Only the id of the video game with the largest value will be displayed. You only selected the videogameid with the largest value. In the second part of the query, videogameids that work with more than 5 developers are selected. Again, no selection headers are connected with the external SQL query.

Modified query:

SELECT videogameid,title
FROM videogames WHERE videogameid IN
(SELECT videogameid
FROM workson
GROUP BY videogameid
HAVING COUNT(DISTINCT developerid)>5
);

This query shows videogameid and title with more than 5 developers

Another query:

SELECT COUNT(developerid) AS dev_count,videogameid FROM workson GROUP BY videogameid
ORDER BY dev_count DESC LIMIT 1;

This shows the videogameid and number of developers for the selected video game that has the most developers. No title .

If we want to see the title:

SELECT videogameid,title FROM videogames WHERE videogameid IN
(SELECT videogameid FROM (SELECT COUNT(developerid) AS count,videogameid FROM workson GROUP BY videogameid
ORDER BY COUNT(developerid) DESC LIMIT 1) AS T);

This query shows the titles and videogameids with the most developers.