CODEWITHSUNDEEP
mysql
Abid Hussain
Abid Hussain

Reputation: 7762

How to get data between two range from mysql with complex string

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

Upvotes: 0

Views: 181

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35593

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

Upvotes: 1

Related Questions