I have two tables (A and B) where my query compares a calculation from table A with a range in table B and then insert a corresponding value to the range(also in table B) in the third table(table C) based on dates. However,it is a possibility that table A may not have data for everyday and for those days i want to enter the value against the second lowest range.
id(PK)|date| v1 | v2
from TableB B
INNER JOIN TableA A ON A.id=B.id
and (IF DATA EXISTS) B.v1+B.v2 between A.min and A.max (ELSE Choose the second lowest A.min)`
What about this
DECLARE myvar INT; SELECT COUNT(*) INTO myvar FROM TableB WHERE Date=p_date; SELECT myvar; IF(myvar>0) SELECT B.Date,A.Value from TableB B left JOIN TableA A ON A.id=B.id WHERE B.id=4 ELSE SELECT p_date AS Date,predefinedvalue AS Value END IF;
You have to use left join because inner join will retrieve only the matching records. Also the predefined value has to be stored in B since if there is no matching record you can't query anything from A.
SELECT CASE WHEN myvar>0 THEN B.Date ELSE p_date END AS Date, CASE WHEN myvar>0 THEN A.Value ELSE predefinedvalue END AS Value from TableB B left JOIN TableA A ON A.id=B.id WHERE B.id=4