John Mason John Mason - 4 months ago 7
MySQL Question

How to write a MySQL statement with multiple conditionals?

I have the following table:

id (integer, primary key)
amount_low (integer)
amount_high (integer)
fixedprice (decimal 4,2 Null)
percentadjust (decimal 4,2 Null)
itemname (varchar 50)


A record will have a value in either the "fixedprice" or "percentadjust" field, but not both. One will be NULL, and the other will have a value.

I need to get records based on a single input amount, "X":

If the "fixedprice" field has a value, I need to get the record if X is >= (fixedprice * amount_low) AND X is <= (fixedprice * amount_high).

If the "percentadjust" field has a value, I need to get the record if X is >= ((((percentadjust / 100) + 1) * 3.5) * amount_low) AND X is <= ((((percentadjust / 100) + 1) * 3.5) * amount_high).

The "3.5" is a value that changes on occasion and I'm not too concerned about that part.

What is a good way to do this in MySQL?

Sample data: (also see http://sqlfiddle.com/#!9/922a0 )

id amount_low amount_high fixedprice percentadjust itemname
-----------------------------------------------------------------
1 20 25 2.25 NULL A
2 50 75 2.38 NULL B
3 23 32 NULL 9.75 C
4 14 22 NULL 9.12 D
5 96 112 2.58 NULL E

Answer

Assuming your X was entered as 111 it would be

select * from tblItems
where (fixedprice is not null and 111>=(fixedprice * amount_low) and 111 <= (fixedprice * amount_high) )
OR (percentadjust is not null and 111>=((((percentadjust / 100) + 1) * 3.5) * amount_low) AND 111<=((((percentadjust / 100) + 1) * 3.5) * amount_high)) 

Note you can always write it as where xyz between A and B to simplify somethings slightly.

Remember that a lot of time can be wasted debugging logic operators when AND and OR are used and safe wrappers with parentheses are not used. So, if you intermingle AND with OR, wrap things well.