Muhamad Amin Apple Muhamad Amin Apple - 1 year ago 82
MySQL Question

Query between 2 columns (Quantity Tier)

I have data in DB like this

Price_Per_Item | Min_Qty | Max_Qty
------------------------------------
10.00 | 1 | 10
20.00 | 10 | 20
30.00 | 20 | 30
40.00 | 30 | NULL


I put
NULL
is for more than 30 because don't have maximum quantity.

So I using this query is working for
Max_Qty
that don't have
NULL
but not working if have
NULL


SELECT Price_Per_Item FROM price WHERE '100' BETWEEN Min_Qty AND Max_Qty

Answer Source
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(price_per_item DECIMAL(5,2) NOT NULL PRIMARY KEY
,Min_Qty INT NOT NULL
,Max_Qty INT NULL
);

INSERT INTO my_table VALUES
(10.00,1,10),
(20.00,10,20),
(30.00,20,30),
(40.00,30,NULL);

SELECT * 
  FROM my_table 
 WHERE min_qty < 100 
   AND (max_qty > 100 OR max_qty IS NULL);
+----------------+---------+---------+
| price_per_item | Min_Qty | Max_Qty |
+----------------+---------+---------+
|          40.00 |      30 |    NULL |
+----------------+---------+---------+
1 row in set (0.01 sec)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download