troubleshooting Question

Searching within Range Values

Avatar of Shafiq Khan
Shafiq KhanFlag for United Kingdom of Great Britain and Northern Ireland asked on
MySQL Server
3 Comments1 Solution478 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros