Select equivalent in MySQL

Question

In my app, I'm using MySQL and what I want to do is to get the equivalent of a certain grade. Like for example, I have a grade of 13, the equivalent of it is 5.0.

I have this type of table structure with data.

    `transmutation` table

    id      grade   equi
    1       0       5.0
    2       14      4.0

And it also come to my mind that I could do this.

    `transmutation` table

    id      grade       equi
    1       01-13       5.0
    2       14-26       4.0

For now, I have this simple syntax in getting equivalent and it's incomplete.

    SELECT equi from transmutation WHERE grade BETWEEN 0 AND 14 AND grade = 0;

Any ideas would be most appreciated.

Answer 1

The simplest way would be to define your table like this:

transmutation table

id      gradeFrom   gradeTo  equi
1       0           13       5.0
2       14          26       4.0

And then:

SELECT equi FROM transmutation WHERE 13 BETWEEN gradeFrom and gradeTo

Answer 2

Well, BETWEEN will not work in this scenario but if you have your table data in second format per post then you can do a simple = equality comparison like below provided that the grade column data format is same

SELECT equi from transmutation WHERE grade = '01-13';