CODEWITHSUNDEEP
mysqlsql
Akshay Jadhav
Akshay Jadhav

Reputation: 33

sql : check min and max range values with database min and max range values

Guys I have an SQL table with min and max range column. Now i want to check the if my new min and max values are already ranges between the database records or not.

Ex. 

    ID Title   Min     Max
     1   A    5001    10000
     2   B    15001   20000

Now the case is that the new range should not fall within the records as it should be either less than of 5000 range or >10000 and <15000 for example. Please suggest the query.

Upvotes: 1

Views: 3629

Answers (3)

ranojan
ranojan

Reputation: 837

SELECT * FROM 0_commission_af_setup where status=0 AND 
stock_id='0102' AND commission_type='discount' AND 
cal_by='0' AND calculation_type='1' AND 
'2' BETWEEN min_range AND max_range OR '3' 
BETWEEN min_range and max_range AND min_range 
BETWEEN '2' AND '3' AND max_range BETWEEN '2'
AND '3' OR min_range>'2' AND max_range<'3'
AND status=0 AND stock_id='0102' 
AND commission_type='discount' AND cal_by='0' 
AND calculation_type='1'

Upvotes: 0

Akshay Jadhav
Akshay Jadhav

Reputation: 33

I got the answer for the same. *note : Upgraded with answer of simimmo's(https://stackoverflow.com/users/4965993/simimmo) answer

SELECT *
FROM table1 
WHERE min < v_new_min AND v_new_max < max
OR v_new_min BETWEEN min AND max 
OR v_new_max BETWEEN min AND max

Now this query will also check the range within inner values of available range. Thanx @simimmo and all guys for your generous response.

Upvotes: 2

Simimmo
Simimmo

Reputation: 668

If the following query returns rows, new range overlaps existing ones.

SELECT *
FROM table1
WHERE v_new_min BETWEEN min AND max
OR v_new_max BETWEEN min AND max

Upvotes: 0

Related Questions