Display parent records that meet condition of child records

In oracle sql, imagine I have a table of game developers and a table of products sold by a game store. Imagine I am trying to select only game developers that have a total amount of products available at the game store that is less than 10.

For the sake of this, I will call the tables ‘Developers’ and ‘Games’. Developers contains a PK of DEV_ID which will serve as the FK of GAME_DEV in Games.

CREATE TABLE Developers (
DEV_ID varchar(5) NOT NULL PRIMARY KEY,
DEV_NAME varchar(20) NOT NULL);

CREATE TABLE Games (
GAME_ID varchar(5) NOT NULL PRIMARY KEY
GAME_NAME varchar(20) NOT NULL,
GAME_PRICE varcher(10) NOT NULL,
GAME_DEV varchar(5) NOT NULL,
CONSTRAINT game_fk FOREIGN KEY (GAME_DEV)
REFERENCES Developers(DEV_ID));

I have tried doing something like creating a view, and then trying to select only the DEV_ID from the view where the amount of entries is less than 10. Heres what ive tried:

CREATE OR REPLACE VIEW games_developers AS
SELECT * FROM Games g
INNER JOIN Developer d
ON g.GAME_DEV = d.DEV_ID;

SELECT DEV_ID FROM games_developers
WHERE COUNT(DEV_NAME) < 10;

Now I get the error message “group function is not allowed here” Any ideas of how I can pull a list of developers who only have an available amount of games at the store that is less than 10?

Answer

One method is:

SELECT d.*
FROM Developer d
WHERE d.DEV_ID IN (SELECT g.GAME_DEV
                   FROM Games g
                   GROUP BY g.GAME_DEV
                   HAVING COUNT(*) < 10
                  );

However, this will miss developers with no games in the store. So:

SELECT d.*
FROM Developer d
WHERE d.DEV_ID NOT IN (SELECT g.GAME_DEV
                       FROM Games g
                       GROUP BY g.GAME_DEV
                       HAVING COUNT(*) >= 10
                      );