We help IT Professionals succeed at work.
Get Started

Searching within Range Values

Shafiq Khan
Shafiq Khan asked
Last Modified: 2012-07-04
I have this issue in which I have two fields in my table called 's_power_from' and 's_power_to' (both set to int) which I use to search for results depending on a certain criteria being met.
The criteria is based on two user-entered values which is checked to see if the entered falls within the range of the fields 's_power_from' and 's_power_to'

A couple of examples so you can understand.

E.g. 1

In my Database:
s_power_from = 2000
s_power_to = 6000

User entered range to check between:
From: 1000
To: 2000

SQL: WHERE s_power_from BETWEEN 1000 AND 2000 OR s_power_to BETWEEN 1000 AND 2000

The above SQL will work fine and the record is displayed

E.g. 2

In my Database:
s_power_from = 2000
s_power_to = 6000

User entered range to check between:
From: 3000
To: 4000

SQL: s_power_from BETWEEN 3000 AND 4000 OR s_power_to BETWEEN 3000 AND 4000

The above SQL does not work as 2000-6000 does fall within the 3000-4000 range
(one of both values should should fall within this criteria, in this case s_power_to = 6000)

I sort of know to why this this is happening but cant get my head around on how to amend the SQL to cater for this requirement.
I'm using an old version of MySQL 3.23, so i'm not sure if that makes any difference to the solution?

Thanks a lot
Watch Question
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE