Creating a view where player experience is greater than required experience of level table - Phpmyadmin

Dani
October 8, 2019
223 views
1 vote
2 Answers

I’ve been trying to create a view with the player’s current level is shown by the amount of experience it has. I have a table called “levels” and a table called “characters”. The idea is that the view contains the level where the player’s experience is greater than the minimum required amount but also less than the next query, so in between.

The table characters:

+-----+------------+------------+
| id  | name       | experience |
+-----+------------+------------+
| 1   | player 1   | 23         |
+-----+------------+------------+

The table levels:

+--------+------------+--------------------+
| level  | level_name | minimum_experience |
+--------+------------+--------------------+
| 1      | Beginner   | 0                  |
| 2      | Protector  | 20                 |
| 3      | Warrior    | 40                 |
+--------+------------+--------------------+

Where as the view I want to create is:

+---------------+----------------+------------+-------+----------------------+----------------------+
| character_id  | character_name | level_name | level | character_experience | next_level_experience|
+---------------+----------------+------------+-------+----------------------+----------------------+
| 1             | player 1       | Protector  | 1     | 23                   | 40                   |
+---------------+----------------+------------+-------+----------------------+----------------------+

One of the queries I use for the view now is, but that doesn’t work.

SELECT
    `experiment`.`characters`.`character_id` AS `character_id`,
    `experiment`.`characters`.`character_name` AS `character_name`,
    `experiment`.`characters`.`experience` AS `current_experience`,
    `experiment`.`levels`.`level` AS `current_level`,
    `experiment`.`levels`.`level_name` AS `level_name`,
    `experiment`.`levels`.`experience` AS `next_levelexp`
FROM
    (
        `experiment`.`characters`
    LEFT JOIN `experiment`.`levels` ON
        (
            (
                `experiment`.`levels`.`experience` < `experiment`.`characters`.`experience`
            )
        )
    )
GROUP BY
    `experiment`.`characters`.`character_id`

The results I get with the query above are;

I hope anyone can help me. I’ve tried a lot but I can’t seem to get it right. Thanks in advance.

Answers

Consider the following:

DROP TABLE IF EXISTS characters;

CREATE TABLE characters
(id SERIAL PRIMARY KEY
,name VARCHAR(12) UNIQUE
,experience INT NOT NULL
);

INSERT INTO characters VALUES
(1,'player 1',23);

DROP TABLE IF EXISTS levels;

CREATE TABLE levels
(level SERIAL PRIMARY KEY
,level_name VARCHAR(12) UNIQUE
,minimum_experience INT NOT NULL
);

INSERT INTO levels VALUES
(1,'Beginner',0),
(2,'Protector',20),
(3,'Warrior',40);

SELECT c.*
     , MAX(x.minimum_experience) minimum_experience
     , MIN(y.minimum_experience) next_level_exp 
  FROM characters c 
  JOIN levels x 
    ON x.minimum_experience <= c.experience 
  LEFT 
  JOIN levels y 
    ON y.minimum_experience > c.experience 
 GROUP 
    BY c.id;
+----+----------+------------+--------------------+----------------+
| id | name     | experience | minimum_experience | next_level_exp |
+----+----------+------------+--------------------+----------------+
|  1 | player 1 |         23 |                 20 |             40 |
+----+----------+------------+--------------------+----------------+

I’ve left out one bit as an exercise for the reader. Hint: it involves another JOIN.

Another Option is to use Correlated Subquery inside the SELECT clause (View on DB Fiddle):

Query

SELECT c.*
     , (SELECT level_name 
        FROM levels 
        WHERE minimum_experience <= c.experience
        ORDER BY minimum_experience DESC 
        LIMIT 1) AS level_name
     , (SELECT level 
        FROM levels 
        WHERE minimum_experience <= c.experience
        ORDER BY minimum_experience DESC 
        LIMIT 1) AS level
     , (SELECT minimum_experience 
        FROM levels 
        WHERE minimum_experience > c.experience
        ORDER BY minimum_experience ASC 
        LIMIT 1) AS next_levelexp
FROM characters c;

Result

| id  | name     | experience | level_name | level | next_levelexp |
| --- | -------- | ---------- | ---------- | ----- | ------------- |
| 1   | player 1 | 23         | Protector  | 2     | 40            |

Please signup or login to give your own answer.

Click here to cancel reply.

Creating a view where player experience is greater than required experience of level table – Phpmyadmin

Answers