SELECT game_co WHERE they've never developed a game for PS4

Question

I have a MariaDB SQL table where I need to find out which game company has never developed a game for a specific console.

Some game_co are PC & Xbox only and have never developed on PS4. Some companies have developed multiple titles on all consoles, but one of their titles was PC only and thus is included in the SELECT. THERE is no field to check if the company is XBOX, PC, Nintendo or PS4 only.

What I've tried is

SELECT game_co, FROM company

WHERE developed_on_ps4 = ('false');

I've added in WHERE clauses but none of them work they all produce the same answer as the code snippet I added above.

This returns too many results and I cant find a way to make sure that developed_on_ps4, has always been false when game_co has been Ubisoft.

Do I need to use a CASE statement or something I'm not sure how that would work after thinking about it.

Answer 1

What you want is all companies where the company name is not in the list of companies where developed_on_ps4=true.

Try something like this:

SELECT DISTINCT game_co FROM company WHERE game_co NOT IN (
  SELECT DISTINCT game_co FROM company where developed_on_ps4='true'
);

If you had rows like so:

game_co    title     developed_on_ps4
-------------------------------------
Ubisoft    Game 1    true
Ubisoft    Game 2    false
Epic       Game 3    true
Blizzard   Game 4    false

Then your inner query:

SELECT DISTINCT game_co FROM company where developed_on_ps4='true';

Would return ('Ubisoft', 'Epic'), making your effective query:

SELECT DISTINCT game_co FROM company WHERE game_co NOT IN ('Ubisoft', 'Epic');

Which would return a single entry, ["Blizzard"]