0

Let's say that we have 3 tables which are videogames, developers, workson.

Under the videogames table, we have attributes such as

  • videogameid (PK)
  • title
  • year
  • genre

Then for developers we have

  • developerid (PK)
  • name
  • gender

Then for the workson table

  • videogameid(PK & FK )
  • developerid (PK & FK )

My attempt at the code:

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

However, I have failed to retrieve the answer with the title ( mainly because I did not select the title) but that's because I can't seem to make the connection.

EDIT: So we have some sample data where for table videogames we have INSERT INTO videogames (videogameid, title,year,genre)
VALUES (111,World of Warcraft ,2004, MMORPG); INSERT INTO videogames (videogameid, title,year,genre) VALUES (112,Starcraft 2 ,2008, RTS);

For the table developers we have INSERT INTO developers( developerid, gender, name) VALUES ( 98734, M, Johnson); INSERT INTO developers( developerid, gender, name) VALUES ( 98735, F, Regina); INSERT INTO developers( developerid, gender, name) VALUES (98736, M , Lamar);

For the table workson INSERT INTO (videogameid, developerid) VALUES (111, 98734); INSERT INTO (videogameid, developerid) VALUES (111,98735); INSERT INTO (videogameid, developerid) Values(112,98736);

The expected output should be the title 'World of Warcraft' because it has the maximum number of directors working on it which is 2 whereas title such as 'Starcraft' does not have the maximum number of developers working on it as seen in this sample data

youfacejaraxxus
  • 97
  • 7
  • "i cant seem to make the connection" - what does that mean? Please share the exact table structure, sample input data, and the expected output, along with your attempts to resolve the problem – Nico Haase Mar 03 '22 at 08:20

1 Answers1

1

Which columns/aggregate function(MAX,COUNT,AVG etc.) you select in the select query will be shown 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 just select the videogameid with the greatest value.In the second part of the query select videogameid that work more than 5 developers.Again there is no title selected to connect to the outer sql query.

Modified version of your 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 videogameid and number of developers that work on selected video game with the maximum amount of developers working on it.There is 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 title and videogameid with the maximum amount of developers working on it.

Yakup Bilen
  • 83
  • 7
  • Please add some explanation to your answer such that others can learn from it – Nico Haase Mar 03 '22 at 08:21
  • Hi i believe there are some syntax errors in your code. Firstly there is no alias used and since this MySQL, there needs to be an alias for outer queries. Secondly, even though I tried adding in my own alias. I still had trouble with the syntax error – youfacejaraxxus Mar 03 '22 at 17:38
  • I edited for the error you got. Also you can look [this page](https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql) for alias. – Yakup Bilen Mar 03 '22 at 19:16