I'm sitting with this SELECT statement
I got ONE product separated into 5 products, each with a column telling how many you should order to get it for that price.
What I want is, for instance, if I'm ordering 54, I should get the price from 50.
If I'm ordering 100 or 101 etc.. I should get the price from 100.. and so on.
Here's my SELECT so far ..
entity.entity_id AS "ID",
entity.value AS "Name",
qty_price.qty AS "Pcs",
qty_price.value AS "Price"
FROM `mg_catalog_product_entity_tier_price` qty_price
JOIN `mg_catalog_product_entity_varchar` entity
ON qty_price.entity_id = entity.entity_id
WHERE entity.entity_id = 300
AND entity.value = (SELECT name FROM `mg_catalog_product_flat_1` WHERE `mg_catalog_product_flat_1`.entity_id = entity.entity_id)
You probably need something like this (note, I am not referring to your exact tables):
SELECT item_id, item_quantity, ( SELECT base_price FROM prices p WHERE o.item_id = p.item_id AND o.item_quantity >= p.base_quantity ORDER BY p.base_quantity LIMIT 1 ) FROM orders o
Here orders is your table of orders, it has item_id and quantity. Prices is a static table having item_id, quantity and base price per item at that quantity. So if you have
17 6 in orders (meaning you are buying 6 of an item with id=17) and
17 1 3.50,
17 5 3.29,
17 100 3.01 (
item_id, quantity, base_price) you will get
17 6 3.29 as a result.