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?