MySQL works with the lowest of multiple columns
I have a table with the following:
+----+-------+-------+-----------+-----------+--------------+--------------+
| id | stock | price | usedStock | usedPrice | specialStock | specialPrice |
+----+-------+-------+-----------+-----------+--------------+--------------+
| #1 | 1 | 10.00 | 0 | 0 | 0 | 0 |
| #2 | 0 | 0 | 1 | 15.00 | 1 | 20.00 |
| #3 | 0 | 0 | 0 | 11.00 | 1 | 14.00 |
+----+-------+-------+-----------+-----------+--------------+--------------+
I would like to create a request that orders at the lowest price if the product type is in stock.
So the query will result in the following order:
#1 - 10.00 (because 10.00 is the lowest result)
#3 - 14.00 (because although 11.00 is less, it not in stock)
#2 - 15.00 (because 15.00 is lower than 20.00)
I added a PHP tag to this question in case of a faster way to compute with PHP after selecting a table.
source to share
I order at the lowest price if the item is in stock:
ORDER BY
LEAST(CASE WHEN stock THEN price ELSE GREATEST(price, usedPrice, specialPrice) END,
CASE WHEN usedStock THEN usedPrice ELSE GREATEST(price, usedPrice, specialPrice) END,
CASE WHEN specialStock THEN specialPrice ELSE GREATEST(price, usedPrice, specialPrice) END)
If the condition is true (> = 1), every CASE WHEN will return the price, otherwise it will return the highest price. Fiddle here .
source to share
I would first create a view
CREATE VIEW stock_min_price AS
SELECT id, LEAST(IF(stock > 0, price, 1000000000),
IF (usedStock > 0, usedPrice, 10000000000),
IF (specialStock > 0, specialPrice, 10000000000)) AS actualPrice
FROM tablename;
then select and order the actual price of all items
SELECT id, IF(actualPrice = 1000000000, NULL, actualPrice) AS price
FROM stock_min_price
ORDER BY actualPrice
// edit: compare s> 0 when stock may be greater than 1
source to share
Honestly, you have to rethink your data modeling for this kind of operation. There is no direct relationship between the three types of stocks, so you must normalize them in a different table. For this I am going to assume that your old table is called oldTable
CREATE TABLE stock(item_id integer, amount integer, price double, type enum('normal', 'used', 'special'));
INSERT INTO stock SELECT o.id, o.stock, o.price,'normal' FROM _oldTable as o
WHERE o.stock >0 OR o.amount >0 ;
INSERT INTO stock SELECT o.id, o.usedStock, o.usedPrice, 'used' FROM _oldTable as o
WHERE o.stock >0 OR o.amount >0 ;
INSERT INTO stock SELECT o.id, o.specialStock, o.specialPrice,'special' FROM _oldTable as o
WHERE o.stock >0 OR o.amount >0 ;
And you have it. As you can see, now you can basically get information with a simple join
stock table.
SELECT o.id, s.kind, min(s.price) FROM _oldTable_ AS o
JOIN stock as s ON s.item_id = o.id AND s.amount > 0
GROUP BY s.item_id
If you want to create output like in the original table, you use:
SELECT o.id, n.amount as amount, n.price as price, u.amount as usedAmount, u.price as usedPrice, s.amount as specialAmount, s.price as specialPrice
FROM _oldTable_ AS o
LEFT JOIN stock as n ON n.item_id = o.id AND n.type = 'normal'
LEFT JOIN stock as u ON u.item_id = o.id AND n.type = 'used'
LEFT JOIN stock as s ON s.item_id = o.id AND n.type = 'special';
Now it is also easier to: a) get the total amount for each in the warehouse (the amount in the amount column with the expression GROUP BY
for item_id); b) get the type of stock used.
ps: you should probably set the index on the column item_id
if you have a lot of elements. Also note that this solution potentially saves you disk space if not all items have all kinds of headroom.
source to share