MySql Function Between.. AND in LEFT JOIN

Posted on August 10, 2013 by Suresh Kamrushi in Mysql

MySql Function Between.. AND in LEFT JOIN

If we are storing range of values in a field in a table and we want to see what are the values falling between these range, we can use “Between.. and” function along with Left Join in Mysql.

Below is the an example to explain it in detail:

1) Table : salary

 Salary table holds fields like id, name and salary.  

 Structure:

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);

 2) Table : grade

 grade table holds fields like class (holds class name for each grade),lower (lower limit of salary for a particular grade), upper (upper limit of salary for particular grade)   

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);

 Run below query you will get class for each employee as per their salary.

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

  Comments or questions are welcome  
Tagged as , ,