Lars Christian Schou Lars Christian Schou - 2 months ago 17
MySQL Question

Assist with MySQL SELECT

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 ..

SELECT
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)
LIMIT 10;


EDIT: Here is the screenshot that was mentioned in the comments to the replies:
Screenshot of Statement and output

Answer

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.