Akanksha
Akanksha

Reputation: 11

In MySQL how to check if a number is between a range of numbers from a series of ranges

I would like to know if we have a MySQL query to check if a number exist between a range of numbers.

I have two tables

ID Number
1 192056
2 143067
ID start Range end Range
1 192000 192100
1 182000 182100
2 163000 163100
2 152000 152100

I need to join these two table on column ID and check if number from 1st table is between the range from the 2nd table.

Output:

192056 - true

143067 - false

Thank you!

Upvotes: 1

Views: 866

Answers (1)

FanoFN
FanoFN

Reputation: 7114

You can try using LEFT JOIN and put the ON condition with >= and <= operator. Here's an example:

CREATE TABLE tableA (
ID INT,
Numbers INT);

INSERT INTO tableA VALUES
(1,192056),
(2,143067);

CREATE TABLE tableB (
ID INT,
start_Range INT,
end_Range INT);

INSERT INTO tableB VALUES
(1,192000,192100),
(1,182000,182100),
(2,163000,163100),
(2,152000,152100);

SELECT * FROM 
  tableA a
LEFT JOIN tableB b 
ON a.Numbers >= b.start_Range 
AND a.Numbers <= b.end_Range;

As for the indication, you may define it using CASE expression:

SELECT a.Numbers, 
       CASE WHEN b.ID IS NULL THEN 'False' ELSE 'True' END AS 'Checking' 
FROM 
  tableA a
LEFT JOIN tableB b 
ON a.Numbers >= b.start_Range 
AND a.Numbers <= b.end_Range

Demo fiddle

Upvotes: 1

Related Questions