1

Well, let's suppose i have the below table with their respective values:

create table player(
name varchar(255),
level int
);

INSERT INTO player values ('Fighter', 4);
INSERT INTO player values ('Archer', 2);
INSERT INTO player values ('Assassin', 6);

And i expect to know which levels below the higher level there is no players.

In this case, i want to have the result: (1,3,5).

I am using MYSQL but any Database query solution could help me to solve.

Thanks.

Bevilaqua
  • 730
  • 1
  • 6
  • 13

1 Answers1

1

You could add a table containing the possible level options when use a query which 1) checks for the non-existence of a player record with the same level, and 2) limits the results to levels lower than the highest possible value.

Here is an example.

CREATE TABLE levelOption (
    level int not null primary key
);

INSERT INTO levelOption ( level )
VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ) , ( 5 ), ( 6 ), ( 7 );

SELECT level
FROM levelOption 
WHERE NOT EXISTS (
        SELECT *
        FROM player
        WHERE player.level = levelOption.level
    )
     AND level < ( SELECT MAX(level) FROM player )
ORDER BY level;

Note that the SELECT MAX subquery assumes that there is at least one record in your player table.

Jeremy
  • 575
  • 4
  • 7