Change sub query variable based upon sub query

[thunderousity]

I have the following SQL statement that selects all the values from a table based upon the row EditionID passed to it via $POST - in this example EditionID is 10

The query gives me 2 values as a tolerance + and - 10 on which to base the list from using BETWEEN

 

However, I want the @tol variable to change to a larger tolerance (lets say 20) if Value1 is >= the integer specified in the variable @trig.

 

I'm not sure how to go about this - as the Value1 values need to be retrieved via the sub queries before making another pass at the sub queries, this time with the correct @tol variable.

 

SET @tol = 10;SET @trig = 300;

 

SELECT EditionID, Make, Model, EditionDesc, Value1FROM t1HAVING Value1BETWEEN(SELECT Value1, TRUNCATE(Value1-@tol) as Value1minFROM t1 WHERE EditionID = 10) iTable1)AND(SELECT Value1, TRUNCATE(Value1+@tol) as Value1maxFROM t1 WHERE EditionID = 10) iTable2)ORDER BY Value1

[justsomeguy]

You would probably need to get the value of the value1 field first in one query, adjust the variables if necessary, then run the other query you have.

[thunderousity]

This works:

 

SET @tol1 = 10;SET @tol2 = 200;SET @trig = 400;SET @car = 15;(n.b. Value1 is an aggregation - for simplicity it is just Value1 - hence using HAVING in the BETWEEN)SELECT EditionID, Make, Model, EditionDesc, Value1FROM t1HAVING Value1BETWEEN(SELECT CASE WHEN Value1 <= @trigTHEN TRUNCATE(Value1-@tol1,1)ELSE TRUNCATE(Value1-@tol2,1)END as Value1minFROM t1 WHERE EditionID = @car) iTable1)AND(SELECT CASE WHEN Value1 <= @trigTHEN TRUNCATE(Value1+@tol1,1)ELSE TRUNCATE(Value1+@tol2,1)END as Value1maxFROM t1 WHERE EditionID = @car) iTable2)ORDER BY Value1