SELECT an additional column from another table based on a value between 2 other values

Question

I have 2 pretty basic tables:

Table: user

id|name|exp
 1|test|270
 2|foo |510

Table: level

level|exp
    1|0
    2|100
    3|250
    4|500

I want to have a pretty simple query, which gives me my level, according to my provided exp-value. Obviously I started with something like:

SELECT * FROM user WHERE id = 1

But then I failed on building a query with a join, which compared my value and finds the "next" level it should return. So in my example, my row with id = 1 should return 3, while id = 2 should return 4.

My expected output would be a table like this:

id|name|exp|level
 1|test|270|3
 2|foo |510|4

Do I even have to join here or is there anything similar? Can I have a subselect? Do I have to check somehow with BETWEEN?

Answer 1

The simplest way to do this would be with a correlated subquery:

SELECT u.*,
       (SELECT l.level FROM level l WHERE u.exp >= l.exp ORDER BY l.exp DESC LIMIT 1) AS level
FROM user u;

See the demo.