Avatar of Shafiq Khan
Shafiq Khan
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Searching within Range Values

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
MySQL Server

Avatar of undefined
Last Comment
Shafiq Khan

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shafiq Khan

Great .. I think that did the trick.  Thank you!
Shafiq Khan

LQ - Search in Range
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck