I'm searching for a solution where I can get results based on a field value directly in my sql statement.
For example if I have the following structure:
calculated_result result_one result_two result_three
1 2.50 3.40 2.90
2 1.90 2.00 3.90
1 1.30 2.23 1.50
Then I want to make a search, where it should return all results where calculated_result is 1 and result_one is higher that 2.30 and smaller or equal to 2.65.
The problem is, that I don't know how to only return rows that fits into the search the user is making.
calculated_result = 1 should check for result_one is bigger and smaller than the rows content calculated_result = 2 should check for result_two is bigger and smaller than the rows content etc
Hope it makes sense.
select *
from myTable
where (calculated_result = 1 and result_one between 2.30 and 2.650)
or (calculated_result = 2 and result_two between 2.30 and 2.650)
or (calculated_result = 3 and result_three between 2.30 and 2.650)
or, if you wanted to use case
select *
from myTable
where case
when calculated_result = 1 then result_one
when calculated_result = 2 then result_two
when calculated_result = 3 then result_three
end between 2.30 and 2.650
Try this
SELECT
*
FROM
table_name
WHERE
calculated_result = 1 AND
result_one > 2.30 AND
result_one <= 2.65
Another:
select *
from myTable
where calculated_result in (1, 2, 3) and
(result_one between 2.30 and 2.650 or result_two between 2.30 and 2.650
or result_three between 2.30 and 2.650)
;
You can try using the CASE operator.
SELECT * FROM yourTable
WHERE (CASE
WHEN calculated_result = 1 THEN result_one
WHEN calculated_result = 2 THEN result_two
END) > 2.3
AND (CASE
WHEN calculated_result = 1 THEN result_one
WHEN calculated_result = 2 THEN result_two
END) <= 2.65