CREATE TABLE IF NOT EXISTS `salary` ( `id` int(11) NOT NULL, `name` varchar(25) NOT NULL,`salary` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;Insert some test values:
INSERT INTO `salary` (`id`, `name`, `salary`) VALUES (1, 'A', 2000),(2, 'B', 3000),(3, 'C', 4000),(4, 'D', 3000),(5, 'E', 2000),(6, 'F', 5000);
CREATE TABLE IF NOT EXISTS `grade` ( `class` varchar(2) NOT NULL,`lower` int(11) NOT NULL, `upper` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;Insert some test values:
INSERT INTO `grade` (`class`, `lower`, `upper`) VALUES ('A1', 2000, 3000),('A2', 3001, 4000),('A3', 4001, 5000),('A4', 5001, 6000);
SELECT salary . * , grade . * FROM `salary` LEFT JOIN grade ON `salary` BETWEEN lower AND upper
id
|
name
|
salary
|
class
|
lower
|
upper
|
1
|
A
|
2000
|
A1
|
2000
|
3000
|
2
|
B
|
3000
|
A1
|
2000
|
3000
|
3
|
C
|
4000
|
A2
|
3001
|
4000
|
4
|
D
|
3000
|
A1
|
2000
|
3000
|
5
|
E
|
2000
|
A1
|
2000
|
3000
|
6
|
F
|
5000
|
A3
|
4001
|
5000
|