Qfyilyi

Employee table

Id  Name Salary frame

1   A    5000   MDF-125NH

2   b    10000  MDF-025AH

3   c    15000  MDF-325KH

4   d    20000  MDF-425LH

5   e    25000  MDF-521MH

I want to get data between to Frame i.e(MDF-125NH, MDF-325KH) from MySQL.

I try something but no success.

SELECT DISTINCT (id) AS ln

FROM employee c04 BETWEEN (

    SELECT SUBSTRING(frame, 0, CHARINDEX('-', frame))

    FROM employee

    ) AND (

    SELECT SUBSTRING(frame, 0, CHARINDEX('-', frame))

    FROM employee

    ) )

OR

SELECT DISTINCT (id) AS ln

FROM employee c04 BETWEEN (

    SELECT SUBSTRING("MDF-125NH", 0, CHARINDEX('-', "MDF-125NH"))

    FROM employee

    ) AND (

    SELECT SUBSTRING("MDF-325KH", 0, CHARINDEX('-', "MDF-325KH"))

    FROM employee

    ) )

CREATE TABLE employee(

   Id     INTEGER  NOT NULL PRIMARY KEY 

  ,Name   VARCHAR(1) NOT NULL

  ,Salary INTEGER  NOT NULL

  ,frame  VARCHAR(9) NOT NULL

);

INSERT INTO employee(Id,Name,Salary,frame) VALUES (1,'A',5000,'MDF-125NH');

INSERT INTO employee(Id,Name,Salary,frame) VALUES (2,'b',10000,'MDF-025AH');

INSERT INTO employee(Id,Name,Salary,frame) VALUES (3,'c',15000,'MDF-325KH');

INSERT INTO employee(Id,Name,Salary,frame) VALUES (4,'d',20000,'MDF-425LH');

INSERT INTO employee(Id,Name,Salary,frame) VALUES (5,'e',25000,'MDF-521MH');



SELECT *

FROM employee 

WHERE frame BETWEEN 'MDF-125NH' and 'MDF-325KH'

;

This results is:

+----+----+------+--------+-----------+

|    | Id | Name | Salary |   frame   |

+----+----+------+--------+-----------+

|  1 |  1 | A    |   5000 | MDF-125NH |

|  2 |  3 | c    |  15000 | MDF-325KH |

+----+----+------+--------+-----------+

'between' in SQL has a very specific meaning which is the equivalent of:

frame >= 'MDF-125NH' and frame <= 'MDF-325KH'

This definition may not necessarily align with what you expect.

+----+----+------+--------+-----------+

|    | Id | Name | Salary |   frame   |

+----+----+------+--------+-----------+

|  1 |  1 | A    |   5000 | MDF-125NH |

|  2 |  2 | b    |  10000 | MDF-025AH | << is this the "between" you want?

|  3 |  3 | c    |  15000 | MDF-325KH |

|  4 |  4 | d    |  20000 | MDF-425LH |

|  5 |  5 | e    |  25000 | MDF-521MH |

+----+----+------+--------+-----------+

This query:

SELECT *

FROM employee 

WHERE id BETWEEN (select min(id) from employee where frame IN ('MDF-125NH','MDF-325KH')) 

         AND (select max(id) from employee where frame IN ('MDF-125NH','MDF-325KH'))

;

produces this result:

+----+----+------+--------+-----------+

|    | Id | Name | Salary |   frame   |

+----+----+------+--------+-----------+

|  1 |  1 | A    |   5000 | MDF-125NH |

|  2 |  2 | b    |  10000 | MDF-025AH |

|  3 |  3 | c    |  15000 | MDF-325KH |

+----+----+------+--------+-----------+

see this demonstrated