Reputation: 11
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
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
Upvotes: 1